Contents
Login Register Wishes Forum About
Language Reference » MySQL Integration
Help topics
Latest updates
2023d  replaceImageMapTitle
2023d  replaceImageMapValues
2023d  createFunctionSerie
2023d  setAbsicssaPosition
2023d  negateValues
Toolbox
  Download as PDF
  Print this page
  Share this page
  Create an account
  Feed the updates!
Google AdSense
MySQL Integration

Data can be retrieved from various sources. In almost all cases data will come from an online database but you can imagine a lot of alternative sources like flat files, XML, scripts,.. You‘ll find here some example on how to interact with the pData class.

Retrieving data from MySQL

MySQL is a powerfull open source database engine. This is the perfect companion for the pChart library but of course you can use any other engine! A lot of databases connectors are shipped with PHP, the syntax to use will depends of the one you‘ll choose. The following example shows how to retrieve data from a MySQL database. In this example we‘ll use a table called measures containing history data for temperature and humidity.



We want here to extract the measured values and the associated timestamp to build an history chart. First step will be to create your pData object and connect to your database :

 /* Include the pData class */
 include("class/pData.class.php");

 /* Create the pData object */
 $MyData = new pData();  

 /* Connect to the MySQL database */
 $db = mysql_connect("localhost", "dbuser", "dbpwd");
 mysql_select_db("pchart",$db);

Now we want to extract each column and bind it to one data serie. This can be done in many ways, the simplest (and not optimised) would be :

 /* Build the query that will returns the data to graph */
 $Requete = "SELECT * FROM `measures`";
 $Result  = mysql_query($Requete,$db);
 while($row = mysql_fetch_array($Result))
  {
   /* Get the data from the query result */
   $timestamp   = $row["timestamp"];
   $temperature = $row["temperature"];
   $humidity    = $row["humidity"];

   /* Save the data in the pData array */
   $myData->addPoints($timestamp,"Timestamp");
   $myData->addPoints($temperature,"Temperature");
   $myData->addPoints($humidity,"Humidity");
  }

But you must prefer to reduce the number of calls to the pData class in order to greatly improve your script speed. As the addPoints method can handle arrays, it would be a nice way to push all the data in a array and pass it to the pData class in only one call :

 /* Build the query that will returns the data to graph */
 $Requete = "SELECT * FROM `measures`";
 $Result  = mysql_query($Requete,$db);
 $timestamp=""; $temperature=""; $humidity="";
 while($row = mysql_fetch_array($Result))
  {
   /* Push the results of the query in an array */
   $timestamp[]   = $row["timestamp"];
   $temperature[] = $row["temperature"];
   $humidity[]    = $row["humidity"];
  }

 /* Save the data in the pData array */
 $myData->addPoints($timestamp,"Timestamp");
 $myData->addPoints($temperature,"Temperature");
 $myData->addPoints($humidity,"Humidity");

Now we want to use the data of the timestamp column as the abscissa labels. We also want to display it in a readable way :

 /* Put the timestamp column on the abscissa axis */
 $myData->setAbscissa("Timestamp");

As we want to chart here to values of different units, we must create a second axis and associate it the humidity :

 /* Associate the "Humidity" data serie to the second axis */
 $myData->setSerieOnAxis("Humidity", 1);

 /* Name this axis "Time" */
 $myData->setXAxisName("Time");

 /* Specify that this axis will display time values */
 $myData->setXAxisDisplay(AXIS_FORMAT_TIME,"H:i");

Now we can make some makeup by specifying the units and axis names :

 /* First Y axis will be dedicated to the temperatures */
 $myData->setAxisName(0,"Temperature");
 $myData->setAxisUnit(0,"°C");

 /* Second Y axis will be dedicated to humidity */
 $myData->setAxisName(1,"Humidity");
 $myData->setAxisUnit(0,"%");

Your dataset is now ready to be charted!
Last updated on 01/06/2011 
by Jean-Damien 
Linked resources
Community comments
  No comments have been posted yet.
© Copyrights
Components used on this web site : Famfamfam icons has been made by Mark James, Rounded corners lite has been coded by Cameron Cooke and Tim Hutchison, SyntaxHighlighter has been written by Alex Gorbatchev. pChart and this web site have been created by Jean-Damien POGOLOTTI. This documentation contains 185 pages and 56 comments. 409 users have registered. This page has been rendered in 0,01 seconds. Wiki revision 1.37.