Using FusionCharts PHP Class - Plotting data from a database

You are viewing documentation for an older version. For current documentation - click here.

Plotting data from a database

In this section, we will show you how to use FusionCharts XT and PHP to plot charts from data contained in a database. Here, we will do the following:

Create a pie chart to show "Production by Factory" by initially using a simple method first.
Convert the chart to use the Data URL method.

We have used MySQL database here. The database dump is present in Download Package > Code > PHPClass > 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 > PHPClass > DBExample folder. The MySQL database dump is present in Download Package > Code > PHPClass > DB.

Before we code the PHP pages to retrieve data, let's quickly have a look at the database structure.

The database contains just two tables:

Factory_Master: To store the name and id of each factory (Columns : FactoryID and FactoryName ) .
Factory_Output: To store the number of units produced by each factory for a given date.(Columns : FactoryId, DatePro, and Quantity) .

For demonstration, we have 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.

The PHP page for this example is named as BasicDBExample.php (in DBExample folder). It contains the following code:

<?php
	//We have included ../Includes/FusionCharts_Gen.php, which contains
	//FusionCharts PHP Class to help us easily embed charts
	//We have also used ../Includes/DBConn.php to easily connect to a database.
	include("../Includes/FusionCharts_Gen.php");
	include("../Includes/DBConn.php");
?>

<HTML>
	<HEAD>
		<TITLE>
			FusionCharts XT - Database Example
		</TITLE>

		<?php
			//You need to include the following JS file, if you intend to embed the chart using JavaScript.
			//Embedding using JavaScript avoids the "Click to Activate..." issue in Internet Explorer
			//When you make your own charts, make sure that the path to this JS file is correct.
			//Else, you will JavaScript errors.
		?>

		<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 have used an MySQL databases containing two tables.

			// Connect to the Database
			$link = connectToDB();

			#  Create pie 3d chart object using FusionCharts PHP Class
			$FC = new FusionCharts("Pie3D","650","450");

			# Set Relative Path of chart SWF file.
			$FC->setSwfPath("../../FusionCharts/");

			# Define chart attributes 
			$strParam="caption=Factory Output report; subCaption=By Quantity; pieSliceDepth=30; showBorder=1; numberSuffix= Units";

			#  Set chart attributes
			$FC->setChartParams($strParam);

			//Fetch all factory records using SQL Query
			//Store chart data values in 'total' column/field and category names in 'FactoryName'
			$strQuery = "select a.FactoryID, b.FactoryName, sum(a.Quantity) as total from Factory_output a, Factory_Master b 
			where a.FactoryId=b.FactoryId group by a.FactoryId,b.FactoryName";

			$result = mysql_query($strQuery) or die(mysql_error());

			//Pass the SQL Query result to the FusionCharts PHP Class function
			//along with field/column names that are storing chart values and corresponding category names
			//to set chart data from database

			if ($result)
			{
				$FC->addDataFromDatabase($result, "total", "FactoryName");
			}

			mysql_close($link);

			# Render the chart
			$FC->renderChart();
		?>

		</CENTER>
	</BODY>
</HTML>

These are the steps that we performed in the above code:

Include FusionCharts_Gen.php, DBConn.php and FusionCharts.js files for easy chart rendering and database connection set up. DBConn.php contains connection parameters to connect to MySQL database.
Create an object of FusionCharts PHP class for Pie 3D chart.
Set relative path of chart SWF file.
Store chart attributes in a variable $strParam .
Set chart attributes through the setChartParams() function.
Fetch factory records and store in $result. The query result creates a column/field total to store chart data and another column FactoryName to store category names.
Add data using the addDataFromDatabase() function passing the column names that store chart data values and category names.
Close database connection.
Render chart by the renderChart() function.

Please go through the FusionCharts PHP Class API Reference section to know more about the functions used in the above code.

When you now run the code, you will get an output as under:

Let's now convert this example to use the Data URL method. As previously explained, in the Data URL 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 will name this page as Default.php.
Data Provider Page - This page provides the XML data to the chart. We will name this page as PieData.php

The pages in this example are contained in Download Package > Code > PHPClass > DB_dataURL folder.

Default.php contains the following code to render the chart:

<?php
	//We have included ../Includes/FusionCharts.php, which contains functions
	//to help us easily embed the charts.
	include("../Includes/FusionCharts.php");
?>
<HTML>
	<HEAD>
		<TITLE>
			FusionCharts XT - Data URL and Database  Example
		</TITLE>
		<?php
			//You need to include the following JS file, if you intend to embed the chart using JavaScript.
			//Embedding using JavaScripts avoids the "Click to Activate..." issue in Internet Explorer
			//When you make your own charts, make sure that the path to this JS file is correct.
			//Else, you will get JavaScript errors.
		?>
		<SCRIPT LANGUAGE="Javascript" SRC="../../FusionCharts/FusionCharts.js"></SCRIPT>
	</HEAD>
	<BODY>
		<CENTER>
		<?php
			//In this example, we show how to connect FusionCharts to a database
			//using FusionCharts PHP class. In our other examples, we have used the Data String method
			//where the XML is generated in the same page as chart. Here, the XML data
			//for the chart will be generated in PieData.php.
			
			//For the sake of ease, we have used an MySQL databases containing two tables.
			
			//the php script in piedata.php interacts with the database,
			//converts the data into proper XML form and finally
			//relays XML data document to the chart
			$strDataURL = "PieData.php";

			//Create the chart - Pie 3D Chart with dataURL as strDataURL
			echo renderChart("../../FusionCharts/Pie3D.swf", $strDataURL, "", "FactorySum", 650, 450, false, false);
		?>
		</CENTER>
	</BODY>
</HTML>

In the above code, we have:

Included FusionCharts.js JavaScript class that uses codes to easily render FusionCharts XT.
Included FusionCharts.php, a PHP wrapper class to load charts easily.
Store the return value of PieData.php in $strDataURL.
Finally, we render the chart using the renderChart() method using the Data URL method.

Note: The renderChart() function used in this code is not the same with the one we used in the previous example, though they bear same name. This is FusionCharts PHP chart embedding function; please go through Using with PHP > Basic Examples to know more about it.

PieData.php contains the following code to output XML Data. This code is similar like the Simple DB Example. The only difference is, here we do not render the chart but send the full XML as output stream.

<?php
	//We have included  ../Includes/DBConn.php, which contains functions
	//to help us easily connect to a database.
	include("../Includes/DBConn.php");
	//We have included ../Includes/FusionCharts_Gen.php, which contains FusionCharts PHP Class
	//to help us easily embed the charts.
	include("../Includes/FusionCharts_Gen.php");

	//This page generates the XML data for the Pie Chart contained in Default.php.

	//For the sake of ease, we have used an MySQL databases containing two tables.

	//Connect to the Database
	$link = connectToDB();

	#  Create a pie 3d chart object  
	$FC = new FusionCharts("Pie3D","650","450");
	
	# Set Relative Path of SWF file. 
	$FC->setSwfPath("../../FusionCharts/");

	#Store chart attributes in a variable 
	$strParam="caption=Factory Output report;subCaption=By Quantity;pieSliceDepth=30;showBorder=1;numberSuffix= Units";

	#Set chart attributes
	$FC->setChartParams($strParam);

	// Fetch all factory records using SQL Query
	// Store chart data values in 'total' column/field and category names in 'FactoryName' 
	$strQuery = "select a.FactoryID, b.FactoryName, sum(a.Quantity) as total from Factory_output a, Factory_Master b 
	where a.FactoryId=b.FactoryId group by a.FactoryId,b.FactoryName";

	$result = mysql_query($strQuery) or die(mysql_error());

	//Pass the SQL Query result to the FusionCharts PHP Class function
	//along with field/column names that are storing chart values and corresponding category names
	//to set chart data from database
	if ($result)
	{
		$FC->addDataFromDatabase($result, "total", "FactoryName");
	}
	mysql_close($link);

	//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
	print  $FC->getXML();
?>

In the above code, we have:

Included FusionCharts_Gen.php and DBConn.php files.
Set connection to database through connectToDB() function.
Created an object of FusionCharts PHP class for Pie 3D chart.
Set the relative path of chart SWF file.
Stored chart attributes in $strParam variable.
Set the chart attributes using setChartParams() function.
Fetched records from database and store the query output in $result.
Passed $result to addDataFromDatabase() function to add chart data.
Wrote the XML to output stream.

Please go through the FusionCharts PHP Class API Reference section to know more about the functions used in the above code.

When you view this page, you will get the same output as before.