Display line and area charts in PHP and MySQL

10:17:00 , , , , 0 Comments



Most data in any web application is usually sourced from a database. Usually some data transformation is required before we can visualize the required data. Most programmers however wanting to display visualizations or charts shy away from the task as there are a number of intermediate steps required to display the visualization. Morris charts however makes the task easier. We only need to directly pass the MySQL queried rows to the Morris charts javascript and we are done. Morris Charts is one of my favorite tools to display dashboard visualization on the web.
Let us see with a simple example. But first go ahead and download the library.

Example

Our first example will show how to display a line chart. Line charts are usually used to display temporal data over time. The following is a sample CRON log MySQL table used to illustrate the examples. The table is used in one of my web applications to record CRON execution times. Every hours some images are processed by the CRON job which is then recorded in the database table.
CREATE TABLE IF NOT EXISTS `cron` (
  `id` int(11) NOT NULL auto_increment,
  `cron_time` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `images_processed` int(11) default '0',
   KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
First include the stylesheets and script files in the header. Adjust the paths according to your directory structure or directly link to a CDN as shown below. Morris depends on the Raphael library to render graphics. However, if you use the CDN path as given below you do not need to download anything.
<link rel="stylesheet" href="//cdnjs.cloudflare.com/ajax/libs/morris.js/0.5.1/morris.css">
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.0/jquery.min.js"></script>
<script src="//cdnjs.cloudflare.com/ajax/libs/raphael/2.1.0/raphael-min.js"></script>
<script src="//cdnjs.cloudflare.com/ajax/libs/morris.js/0.5.1/morris.min.js"></script>
In the following code example I will display the last 24 CRON execution timings as a line chart. First we will get the data from MySQL.
$rows = '';
$query = "SELECT * FROM cron ORDER BY cron_time DESC LIMIT 0, 24";
$result = mysqli_query($con,$query);
$total_rows =  $result->num_rows;
if($result) 
{
    $rows = mysqli_fetch_all($result, MYSQLI_ASSOC);
}
The $rows variable now contains the data array which will be used to display the line chart. The array will be like the following:
Array
(
    [0] => Array
        (
            [id] => 606
            [cron_time] => 2015-02-04 23:05:34
            [images_processed] => 65
        )
 
    [1] => Array
        (
            [id] => 605
            [cron_time] => 2015-02-04 23:00:02
            [images_processed] => 12
        )
.
.
The data is used below to display the chart. Note we need to encode the data to json before we pass it to Morris charts. Morris charts expects the data as a JSON array.
.
.
data: [
    { year: '2008', value: 20 },
    { year: '2009', value: 10 },
    { year: '2010', value: 5 },
    { year: '2011', value: 5 },
    { year: '2012', value: 20 }
  ],
Next we define a div where we want to display the chart.
<div id="morris-line-chart"></div>
<script>
 
Morris.Line({
    // ID of the element in which to draw the chart.
    element: 'morris-line-chart',
 
    // Chart data records -- each entry in this array corresponds to a point
    // on the chart.
    data: <?php echo json_encode($rows);?>,
 
    // The name of the data record attribute that contains x-values.
    xkey: 'cron_time',
 
    // A list of names of data record attributes that contain y-values.
    ykeys: ['images_processed'],
 
    // Labels for the ykeys -- will be displayed when you hover over the
    // chart.
    labels: ['Images Processed'],
 
    lineColors: ['#0b62a4'],
    xLabels: 'hour',
 
    // Disables line smoothing
    smooth: true,
    resize: true
});
</script>
This will display the line chart as below.
morris-chart1
If you need to plot another variable then you can add the same to the javascript data option. Say we need to add a column from the db table like ‘images_failed’ to the graph, we can do the it as below.
<script>
 
Morris.Line({
    // ID of the element in which to draw the chart.
    element: 'morris-line-chart',
 
    // Chart data records -- each entry in this array corresponds to a point
    // on the chart.
    data: <?php echo json_encode($rows);?>,
 
    // The name of the data record attribute that contains x-values.
    xkey: 'cron_time',
 
    // A list of names of data record attributes that contain y-values.
    ykeys: ['images_processed', 'images_failed'],
 
    // Labels for the ykeys -- will be displayed when you hover over the
    // chart.
    labels: ['Images Processed', 'Images Failed'],
 
    // Add different line colors to each variable
    lineColors: ['#0b62a4' '#D58665'],
    xLabels: 'hour',
 
    // Disables line smoothing
    smooth: true,
    resize: true
});
</script>
This will display the 2 variable line chart.
morris-chart2
We can also display the above as a area chart by changing the javascript function name from ‘Morris.Line’ to ‘Morris.Area’. This will display the following without changing any other option.
morris-chart3
There are a variety of options to customize the charts.

0 comments :

Subscribe to: Post Comments ( Atom )