FusionCharts and PHP> Using dataURL method |
Here, we'll be using PHP as our choice of scripting language and create the dynamic "Top 5 Countries" 3D column chart, with MS SQL Server as our database, using the dataURL method. We'll be using VB.NET as our choice of .NET language. Please do read the section "Using with scripts and database", if you've not already read, before reading this section. As we had earlier seen, the dataURL method requires two pages:
The above two are present in the downloads under the folder FusionCharts2_3 > Sample Code > DBExamples > PHP > dataURL. We'll also need two other pages:
The above two include files are present under FusionCharts2_3 > Sample Code > DBExamples > PHP folder. FC_Colors.php looks as under: |
<?php |
Basically, this file is a list of hex colors that we'll be using for the chart. In the above list, we've stored only 20 colors. You can extend it to any number. You should note that all the above colors are without the #. We'll include this file in all our pages, where we generate the XML data for FusionCharts. |
Connection_ini.php is the page which contains the database connection details. When you run the code on your machine, after downloading the package, you should ensure that this page is updated so as to reflect the connection details to your SQL server. This page looks as under: |
<?php |
The page Data.php is responsible for relaying virtual XML documents to the FusionCharts embedded in Chart.php. You should note that Data.php does NOT write XML data documents to the server disk physically. It relays the XML data document virtually (i.e., no XML files are created and saved on the server). The code for this page can be reproduced as under: |
<?php //Include connection and colors file include '../FC_Colors.php'; include '../Connection_ini.php'; //Connect to the ODBC Source $sqlconnect=odbc_connect($dsn,$username,$password); //strSQL - String variable to contain the SQL query $strSQL="SELECT TOP 5 Country, SUM(ExtendedPrice) As Total, COUNT(DISTINCT OrderID) As orderNumber FROM Invoices WHERE YEAR(OrderDate)=" . $_GET["year"] . " GROUP BY Country ORDER BY SUM(ExtendedPrice) DESC"; //strXMLDataData - String variable to contain the entire XML data document for the chart //Initialize the <graph> element. $strXMLData = "<graph caption='Top 5 Countries for the Year " . $_GET["year"] . "' shownames='1' showvalues='0' decimalPrecision='0' numberPrefix='$'>" . chr(13); $intCounter=0; //Get the recordset $oRs=odbc_exec($sqlconnect, $strSQL); //Iterate through each row to get the data while(odbc_fetch_row($oRs)) { //Append the value in format <set name='...' value='...' color='...' /> $strXMLData = $strXMLData . "<set name='" . odbc_result($oRs,"Country") . "' color='" . $strColor[$intCounter] . "' value='" . odbc_result($oRs,"Total") . "'/>" . chr(13); //Increment the counter $intCounter=$intCounter+1; } odbc_close($sqlconnect); //Entire XML - concatenation $strXMLData = $strXMLData . "</graph>"; //Write the XML data to output stream echo trim($strXMLData); ?> |
In the above code, we're doing the following:
When you try viewing this page individually in the browser (in the format http://localhost/...Path.../DBExamples/PHP/dataURL/Data.php?year=1996), you'll get the following XML output. You should always make sure, that while using dataURL method, your dataURL script should output just XML data and no HTML tags. |
<graph caption='Top 5 Countries for the Year 1996' shownames='1'
showvalues='0' decimalPrecision='0' numberPrefix='$'> <set name='USA' value='38105.68' color='AFD8F8'/> <set name='Germany' value='35407.15' color='F6BD0F'/> <set name='Austria' value='25601.34' color='8BBA00'/> <set name='Brazil' value='20148.82' color='A66EDD'/> <set name='France' value='17372.76' color='F984A1'/> </graph> |
This is exactly the XML that we need for our 3D Column Chart. So, let's now shift our attention to Chart.php and see the code it contains to attain the following tasks:
Chart.php contains the following code: |
<HTML> <HEAD> <TITLE>FusionCharts</TITLE> <LINK REL='Stylesheet' HREF='../Style.css'> </HEAD> <?php //Include connection and colors file include '../Connection_ini.php'; include '../FC_Colors.php'; //Connect to the ODBC Source $sqlconnect=odbc_connect($dsn,$username,$password); //Generate the query to get a list of unique years $strSQL="SELECT DISTINCT YEAR(OrderDate) As Year FROM Orders ORDER BY 1"; //Get
the list of years |
Here, in the above code, we're doing the following:
When you now see Chart.php in your browser, you'll get the following output: |