Create stunning charts using HTML5, Javascript + MYSQL

There are plenty of nice javascript libraries out there to create excellent HTML5 charts. Unfortunatelly I didn’t find one which also has samples on how to load data from a MYSQL database, although it is very simple to reliase such a task.

In this tutorial, I’d like to show you how you can create a chart using the http://canvasjs.com/ library and some PHP/MYSQL code to load data.You need the library from CanvasJS and for the data querys to MYSQL I’m going to use the fantastic  PEAR MDB2 library

Let’s assume for example you have a table with this data:

Date Calls Emails
201301 5268 2566
201302 5411 2869
201303 4855 2569
201304 4696 2789
201305 5125 3111

This is the necessary Javascript Code to set up title, and various options.

    var chart = new CanvasJS.Chart("chartContainer", {
        zoomEnabled: true,
      title:{
        text: "My first Chart"
      },
      legend: {
       horizontalAlign: "center", // "center" , "right"
       verticalAlign: "bottom",  // "top" , "bottom"
       fontSize: 13,
       fontFamily: "Verdana"
      },
      data: [//array of dataSeries
        { //dataSeries object
         /*** Change type "column" to "bar", "area", "line" or "pie"***/
         type: "line",
        showInLegend: true,
      legendText: "Calls",
         dataPoints: [

first of all, we need to create the connection to the database and load the data into an array. This can be done using the following statement:

require_once 'MDB2.php';
$dsn = array(
    'phptype'  => 'mysql',
    'username' => '---DBUSER---',
    'password' => '---PASSWORD---',
    'hostspec' => '---HOST---', //normally localhost
    'database' => '---DATABASE---',
);
$mdb2 =& MDB2::connect($dsn, $options);
if (PEAR::isError($mdb2)) {
    die($mdb2->getMessage());
}

This is where the “magic” comes in. Now we create the ‘dataPoints’ for our chart. This envolves to create the javascript code:

// load data for first line
$query1 = "SELECT date, calls FROM MYTABLE";
 
$res =&$mdb2->query($query1);
while (($row = $res->fetchRow())) {
echo "{ label: ".$row[0].", y: ".$row[1]." },\n";
}
?>

Then we need to insert the necessary javascript code for creating our second line:

] // close previous dataPoints
}, // close previosus line 
{ // open next line and set some options
                                type: "line",
                                showInLegend: true,
                                name: "Email",
                                lineThickness: 2,
 
                                dataPoints: [

And do the next query:

// load data for second line
$query2 = "SELECT date, emails FROM MYTABLE";
 
$res =&$mdb2->query($query2);
while (($row = $res->fetchRow())) {
echo "{ label: ".$row[0].", y: ".$row[1]." },\n";
}
?>

close the graphs up and render the chart:

         ]
}
       ]
     });
    chart.render();

and add the DIV Container to show the graph on your page:

<body>
  <div id="chartContainer" style="height: 300px; width: 800px;">
  </div>
</body>
</html>

You can download the complete script example by clicking here

Leave a Reply