Populate Jqplot Bar Chart From JSON

First posted on 02/11/2013

Download all the necessary Jqplot plugins in order to use it in this application.

Full source code can be downloaded here.

Few things that need to be set:

  1. Database (MySQL)
  2. Server-side (PHP)
  3. Data exchange (JSON)
  4. Interface (Jqplot)

Setting up database

  1. Create a database named: tpcp
  2. Execute the following SQL statement:
/*
Navicat MySQL Data Transfer

Source Server : localhost
Source Server Version : 50612
Source Host : 127.0.0.1:3306
Source Database : tpcp

Target Server Type : MYSQL
Target Server Version : 50612
File Encoding : 65001

Date: 2013-11-01 11:33:57
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for graph
-- ----------------------------
DROP TABLE IF EXISTS `graph`;
CREATE TABLE `graph` (
  `STAFF_ID` int(15) NOT NULL,
  `STAFF_NAME` varchar(50) NOT NULL,
  `S_BASE` int(3) NOT NULL,
  `S_KEY` int(3) NOT NULL,
  `S_PACING` int(3) NOT NULL,
  `S_EMERGING` int(3) NOT NULL,
  `P_BASE` int(3) NOT NULL,
  `P_KEY` int(3) NOT NULL,
  `P_PACING` int(3) NOT NULL,
  `P_EMERGING` int(3) NOT NULL,
  `C_BASE` int(3) NOT NULL,
  `C_KEY` int(3) NOT NULL,
  `C_PACING` int(3) NOT NULL,
  `C_EMERGING` int(3) NOT NULL,
PRIMARY KEY (`STAFF_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Create a server-side scripting in php

Create a connection.php to connect the tpcp database

<?php
    session_start();
     
    $host="localhost"; // Host name
    $username="root"; // Mysql username
    $password=""; // Mysql password
    $db_name="tpcp"; // Database name
     
    mysqli_connect("$host", "$username", "$password") or die("cannot connect");
    mysqli_select_db("$db_name") or die("cannot select DB");
?>

Retrieve from database

In data.php, we retrieve rows from our database previously and encode it to json. What we're going to do with these values is to calculate the total of each values in the column and display it in a bar chart.

  1. Make sure to include the connection.php

    include("connection.php");

  2. Construct a SQL query for retrieving data from our db

    $sql = mysql_query("SELECT * FROM graph");

  3. To accumulate each row values according to its column by adding previous value to the latest one:

    while($row = mysql_fetch_array($sql)){
        $totSB += $row['S_BASE'];
        $totSK += $row['S_KEY'];
        $totSP += $row['S_PACING'];
        $totSE += $row['S_EMERGING'];
    
        $totPB += $row['P_BASE'];
        $totPK += $row['P_KEY'];
        $totPP += $row['P_PACING'];
        $totPE += $row['P_EMERGING'];
    
        $totCB += $row['C_BASE'];
        $totCK += $row['C_KEY'];
        $totCP += $row['C_PACING'];
        $totCE += $row['C_EMERGING'];
    }
    
  4. Assign each variables into an array with two key NAME and VALUES. The formatting for the VALUES are as follows: [[S],[P],[C]]. This is important for jqplot to read.

    $data = ['NAME' => 'Graph title',
    'VALUES' => [[$totSB, $totSK, $totSP, $totSE],[$totPB, $totPK, $totPP, $totPE],
    [$totCB, $totCK, $totCP, $totCE]]];
    
  5. Encode the array in json format

    header('Content-Type: application/json');
    print json_encode([$data]);
    

Create a bar chart using Jqplot using json encoded strings from data.php

  1. In order to use Jqplot, you must include all necessary styles and javascript files

<head>

<link class="include" rel="stylesheet" type="text/css" href="jquery.jqplot.min.css" />
<script class="include" type="text/javascript" src="jquery.min.js"></script>

End of <body>

<script class="include" type="text/javascript" src="dist/jquery.jqplot.min.js"></script>
<script class="include" type="text/javascript" src="dist/plugins/jqplot.barRenderer.min.js"></script>
<script class="include" type="text/javascript" src="dist/plugins/jqplot.pieRenderer.min.js"></script>
<script class="include" type="text/javascript" src="dist/plugins/jqplot.categoryAxisRenderer.min.js"></script>
<script class="include" type="text/javascript" src="dist/plugins/jqplot.pointLabels.min.js"></script>
<script class="include" type="text/javascript" src="dist/plugins/jqplot.json2.min.js"></script>
  1. Instantiation of the barchart. id="chart2" refers to our Jquery part.

    <div id="chart2" style="margin-top:20px; margin-left:20px; width:500px; height:500px;"></div>

  2. Populate Jqplot from JSON data

<script type="text/javascript">
 
        jQuery(document).ready(function() {
            urlDataJSON = 'data.php';
 
            $.getJSON(urlDataJSON, function (data) {
 
                console.log(data);
 
                var tick = ['Base','Key','Pacing','Emerging',];
                var types = ['S','C','P'];
 
                var options = {
                    legend: {
                        show: true,
                        location: 'ne',
                        placement: "outside",
                        labels: types
                    },
                    title: data[0].NAME,
                    seriesDefaults: {
                        renderer: $.jqplot.BarRenderer,
                        pointLabels: {
                            show: true,
                            location: 'e',
                            edgeTolerance: -15
                        },
                        shadowAngle: 130,
                        renderOptions: {
                            barDirection: 'horizontal'
                        }
                    },
                    axes: {
                        xaxis: {
                            renderer: $.jqplot.CategoryAxisRenderer,
                            ticks: tick
                        }
                    }
                };
                var plot = $.jqplot('chart2', data[0].VALUES, options);
            });
    });
</script>

P/s: To Ema, yes, I took your assignment for this example.

Show Comments

Get the latest posts delivered right to your inbox.