## Using FusionCharts with PHP > Plotting data from a database
In this section, we'll show you how to use FusionCharts and PHP to plot charts from data contained in a database. We'll create a pie chart to show "Production by Factory" using: dataXML method first. Thereafter, we'll convert this chart to use dataURL method. We've used MySQL database here. The database dump is present in Download Package > Code > PHP > DB folder. You can, however, use any database with FusionCharts including MS SQL, Oracle, Access etc. Before you go further with this page, we recommend you to please see the previous section "Basic Examples" as we start off from concepts explained in that page. The code examples contained in this page are present in Download Package > Code > PHP > DBExample folder. The MySQL database dump is present in Download Package > Code > PHP > DB.
Database Structure
Before we code the PHP pages to retrieve data, let's quickly have a look at the database structure.
The database contains just 2 tables: Factory_Master: To store the name and id of each factory Factory_Output: To store the number of units produced by each factory for a given date. For demonstration, we've fed some dummy data in the database. Let's now shift our attention to the PHP page that will interact with the database, fetch data and then render a chart.
Building the PHP Page for dataXML Method
The PHP page for dataXML method example is named as BasicDBExample.php (in DBExample folder). It contains the following code:
<?php //We've included ../Includes/FusionCharts.php and ../Includes/DBConn.php, which contains //functions to help us easily embed the charts and connect to a database. include("../Includes/FusionCharts.php"); include("../Includes/DBConn.php"); ?> <HTML> <HEAD> <TITLE>FusionCharts - Database Example</TITLE> <SCRIPT LANGUAGE="Javascript" SRC="../../FusionCharts/FusionCharts.js"></SCRIPT> </HEAD> <BODY> <CENTER> <?php //In this example, we show how to connect FusionCharts to a database. //For the sake of ease, we've used a MySQL database containing two //tables. //Connect to the DB $link = connectToDB(); //$strXML will be used to store the entire XML document generated //Generate the chart element $strXML = "<chart caption='Factory Output report' subCaption='By Quantity' pieSliceDepth='30' showBorder='1' formatNumberScale='0' numberSuffix=' Units'>"; //Fetch all factory records $strQuery = "select * from Factory_Master"; $result = mysql_query($strQuery) or die(mysql_error()); //Iterate through each factory if ($result) { while($ors = mysql_fetch_array($result)) { //Now create a second query to get details for this factory $strQuery = "select sum(Quantity) as TotOutput from Factory_Output where FactoryId=" . $ors['FactoryId']; $result2 = mysql_query($strQuery) or die(mysql_error()); $ors2 = mysql_fetch_array($result2); //Generate <set label='..' value='..'/> $strXML .= "<set label='" . $ors['FactoryName'] . "' value='" . $ors2['TotOutput'] . "' />"; //free the resultset mysql_free_result($result2); } } mysql_close($link); //Finally, close <chart> element $strXML .= "</chart>"; //Create the chart - Pie 3D Chart with data from $strXML echo renderChart("../../FusionCharts/Pie3D.swf", "", $strXML, "FactorySum", 600, 300, false, false); ?> </BODY> </HTML>
The following actions are taking place in this code: We first include FusionCharts.js JavaScript class and FusionCharts.php , to enable easy embedding of FusionCharts. We then include DBConn.php, which contains connection parameters to connect to MySQL database. Thereafter, we generate the XML data document by iterating through each record and store it in strXML variable. Finally, we render the chart using renderChart() method and pass strXML as dataXML. When you now run the code, you'll get an output as under:
Converting the example to use dataURL method
Let's now convert this example to use dataURL method. As previously explained, in dataURL mode, you need two pages: Chart Container Page - The page which embeds the HTML code to render the chart. This page also tells the chart where to load the data from. We'll name this page as Default.php. Data Provider Page - This page provides the XML data to the chart. We'll name this page as PieData.php The pages in this example are contained in Download Package > Code > PHP > DB_dataURL folder.
Chart Container Page - Default.php
Default.php contains the following code to render the chart:
//We've included ../Includes/FusionCharts.php, which contains functions //to help us easily embed the charts. include("../Includes/FusionCharts.php"); ?> <HTML> <HEAD> <TITLE> FusionCharts - dataURL and Database Example</TITLE> <SCRIPT LANGUAGE="Javascript" SRC="../../FusionCharts/FusionCharts.js"></SCRIPT> </HEAD> <BODY> <?php //In this example, we show how to connect FusionCharts to a database //using dataURL method. In our previous example, we've used dataXML method //where the XML is generated in the same page as chart. Here, the XML data //for the chart would be generated in PieData.php. //To illustrate how to pass additional data as querystring to dataURL, //we've added an animate property, which will be passed to PieData.php. //PieData.php would handle this animate property and then generate the //XML accordingly. //For the sake of ease, we've used an an MySQL databases containing two //tables. //Set DataURL with animation property to 1 //NOTE: It's necessary to encode the dataURL if you've added parameters to it $strDataURL = encodeDataURL("PieData.php?animate=1"); //Create the chart - Pie 3D Chart with dataURL as strDataURL echo renderChart("../../FusionCharts/Pie3D.swf", $strDataURL, "", "FactorySum", 600, 300, false, false); ?> </BODY> </HTML>
In the above code, we're: Including FusionCharts.js JavaScript class and FusionCharts.php Create the dataURL string and store it in strDataURL variable. We append a dummy propery animate to show how to pass parameters to dataURL. After building the dataURL, we encode it using encodeDataURL function defined in FusionCharts.php. Finally, we render the chart using renderChart() method and set dataURL as strDataURL.
Creating the data provider page PieData.php
PieData.php contains the following code to output XML Data:
<?php //We've included ../Includes/DBConn.php, which contains functions //to help us easily connect to a database. include("../Includes/DBConn.php"); //This page generates the XML data for the Pie Chart contained in //Default.php. //For the sake of ease, we've used an an MySQL databases containing two //tables. //Connect to the DB $link = connectToDB(); //Default.php has passed us a property animate. We request that. $animateChart = $_GET['animate']; //Set default value of 1 if ($animateChart=="") $animateChart = "1"; //$strXML will be used to store the entire XML document generated //Generate the chart element $strXML = "<chart caption='Factory Output report' subCaption='By Quantity' pieSliceDepth='30' showBorder='1' formatNumberScale='0' numberSuffix=' Units' animation=' " . $animateChart . "'>"; //Fetch all factory records $strQuery = "select * from Factory_Master"; $result = mysql_query($strQuery) or die(mysql_error()); 'Iterate through each factory if ($result) { while($ors = mysql_fetch_array($result)) { //Now create a second query to get details for this factory $strQuery = "select sum(Quantity) as TotOutput from Factory_Output where FactoryId=" . $ors['FactoryId'] $result2 = mysql_query($strQuery) or die(mysql_error()); $ors2 = mysql_fetch_array($result2); //Generate <set label='..' value='..' /> $strXML .= "<set label='" . $ors['FactoryName'] . "' value='" . $ors2['TotOutput'] . "' />"; //free the resultset mysql_free_result($result2); } } mysql_close($link); //Finally, close <chart> element $strXML .= "</chart>"; //Set Proper output content-type header('Content-type: text/xml'); //Just write out the XML data //NOTE THAT THIS PAGE DOESN'T CONTAIN ANY HTML TAG, WHATSOEVER echo $strXML; ?>
In the above page: We first request the animate property which has been passed to it (from dataURL) We generate the data and store it in strXML variable Finally, we write this data to output stream without any HTML tags. When you view this page, you'll get the same output as before.