 |
Databases
To get the chart's data from a database, use a scripting language to communicate with the database, collect the data and format it in the same way as static data, and then pass it to the tool.
To make this task simple, start with static data typed manually, then replace each static value with a dynamic value retrieved from a database. The result should be identical whether it is constructed with static or dynamic values. Keep in mind that this process is completely independent from the tool, and that the tool displays the data exactly in the same way when it is formated properly.
|
The number of databases and techniques to access them is too big to cover in detail. For detailed information, please refer to the scripting and database manuals.
Here's an example that shows how to get the data from a MySQL database with a PHP script. This example assumes that the database is already created, and that it has a table called Growth that looks like this:
Database: Accounting
Table: Growth
| Region | Year | Revenue |
| Region A | 2006 | 5 |
| Region A | 2007 | 10 |
| Region A | 2008 | 30 |
| Region A | 2009 | 63 |
| Region B | 2006 | 100 |
| Region B | 2007 | 20 |
| Region B | 2008 | 65 |
| Region B | 2009 | 55 |
| Region C | 2006 | 56 |
| Region C | 2007 | 21 |
| Region C | 2008 | 5 |
| Region C | 2009 | 90 |
The task is to read and organize the data like this:
| | 2006 | 2007 | 2008 | 2009 |
| Region A | 5 | 10 | 30 | 63 |
| Region B | 100 | 20 | 65 | 55 |
| Region C | 56 | 21 | 5 | 90 |
Then, create the corresponding chart_data XML structure. Here is one way to do this in PHP:
<?php
//connect to the database
mysql_connect ( "host", "user", "password" );
mysql_select_db ( "Accounting" );
//start the XML output
print "<chart>";
print "<chart_data>";
//output the first row that contains the years
print "<row>";
print "<null/>";
$category = mysql_query ("SELECT Year FROM Growth GROUP BY Year ORDER BY Year");
for ( $column=0; $column < mysql_num_rows($category); $column++ ) {
print "<string>".mysql_result ( $category, $column, "Year")."</string>";
}
print "</row>";
//output row 2 to 4. Each row contains a region name and its data
$series = mysql_query ("SELECT Region FROM Growth GROUP BY Region ORDER BY Region");
for ( $row=0; $row < mysql_num_rows($series); $row++ ) {
print "<row>";
$region = mysql_result ( $series, $row, "Region");
print "<string>$region</string>";
$data = mysql_query ("SELECT Revenue FROM Growth WHERE Region='$region' ORDER BY Year");
for ( $column=0; $column < mysql_num_rows($data); $column++ ) {
print "<number>".mysql_result ( $data, $column, "Revenue")."</number>";
}
print "</row>";
}
//finish the XML output
print "</chart_data>";
print "</chart>";
?>
|
The script's output is:
<chart>
<chart_data>
<row>
<null/>
<string>2006</string>
<string>2007</string>
<string>2008</string>
<string>2009</string>
</row>
<row>
<string>Region A</string>
<number>5</number>
<number>10</number>
<number>30</number>
<number>63</number>
</row>
<row>
<string>Region B</string>
<number>100</number>
<number>20</number>
<number>65</number>
<number>55</number>
</row>
<row>
<string>Region C</string>
<number>56</number>
<number>21</number>
<number>5</number>
<number>90</number>
</row>
</chart_data>
</chart>
|
Notice that the result is identical to the XML generated manually. The tool itself does not differentiate between this code generated by a script and a static XML code generated manually.
| |