Display line and area charts in PHP and MySQL

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.


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.

  `id` int(11) NOT NULL auto_increment,
  `cron_time` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `images_processed` int(11) default '0',
   KEY `id` (`id`)

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.


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;
    $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:

    [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.


This will display the line chart as below.


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.


This will display the 2 variable line chart.


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.


There are a variety of options to customize the charts.

4 thoughts to “Display line and area charts in PHP and MySQL”

  1. I’m working on school project for questionnaire and I could not retrieve data saved in mySQL to visualize.
    I try your technique, it did not work for me. can you upload the demo.

  2. Hi Sameer,
    Thanks for the example.
    i have tried with the above example. But its not working for me. I am just a beginner in this, could you please share the full code of the above example?

Comments are closed.