Active Graphs and Charts - Dynamic Data from Databases

Getting Started

Documentation

Tutorials

Examples

Dynamic Data from Databases

In this tutorial we show how to use the graphing package to plot data directly from database in real time.

The bar graph below is plotting data from a MySQL databases which contains sales data. Upon the load of this page the graph will issue a request (via an Ajax call) to a server script for the data. The server script will read the data from the database and return to the graph software. The values are then plotted on the graph.

Configure the Graph

The first step is to configure the graph software to request the data from a server script. This is done with the parameter 'JpGraphDataFile'. Here is the configuration used for this graph:-

    window.onload = function () {
        /**
         * draw the graph
         */
        var thegraph = new JpGraph('vertical bar',
                                   'bargraph',
                                   null,
                                   {
                                   JpGraphDataFile       : './scripts/bardata-mysql.php',
                                   JpGraphPropertyFile   : './scripts/barprops.txt',
                                   });
    }

Create the Data Script

For this example we have created the data script with PHP. The server side script can be written in any server side language and at the bottom of this page there are sample scripts for ASP, JSP and PHP

The script is fairly straight forward and performs the following steps:-

Connect to the Database
Issue the Query
Read through the results and output in the format required for the graph

<?php
/*
 * connect to the database
 */
$dbLink = mysql_connect("localhost:3306",
                        "[DATABASE_USER]",
                        "[DATABASE_PASSWORD]");
if (!$dbLink) 
{
        print "Could not connect: ".mysql_error();
        exit(0);
}
if (!mysql_select_db("graphtestdb", $dbLink)) 
{
        print "Could not select database: ".mysql_error();
        exit(0);
}
/*
 * read the data from the result set and output 
 * to the graphing software
 */
$sql    = "SELECT productx,producty,productz FROM sales ORDER BY month ";
$result = mysql_query($sql, $dbLink);
$dataNum = 1;
while ( $row = mysql_fetch_array($result,MYSQL_ASSOC) ) 
{
        echo "series1data" . $dataNum . " = " . $row["productx"] . PHP_EOL;
        echo "series2data" . $dataNum . " = " . $row["producty"] . PHP_EOL;
        echo "series3data" . $dataNum . " = " . $row["productz"] . PHP_EOL;
        $dataNum++;
}
/*
 * release the result set and close the databse connection
 */
mysql_free_result($result);
mysql_close($dbLink);
/*
 * all finished so exit
 */
exit(0);
?>

Formatting the Data Results

In the above example we are reading monthly data for 3 products from the database. For each product we have a series of data, in this case 12 values representing each month of the year. The data should be output in the following format:-

   seriesMdataN = [DATA VALUE]

where 'M' is the series number and N the data number. Here is a sample output of the script:-

series1data1 = 345
series2data1 = 100
series3data1 = 800
series1data2 = 445
series2data2 = 200
series3data2 = 740
series1data3 = 945
series2data3 = 300
series3data3 = 680
series1data4 = 1145
series2data4 = 400
series3data4 = 697
series1data5 = 780
series2data5 = 450
series3data5 = 524
series1data6 = 640
series2data6 = 700
series3data6 = 100
series1data7 = 545
series2data7 = 800
series3data7 = 200
series1data8 = 445
series2data8 = 900
series3data8 = 340
series1data9 = 345
series2data9 = 800
series3data9 = 280
series1data10 = 245
series2data10 = 700
series3data10 = 298
series1data11 = 180
series2data11 = 650
series3data11 = 240
series1data12 = 440
series2data12 = 625
series3data12 = 150

Sample Data Scripts

The server script may be written in any language, here are sample scripts for some common languages:-

Database Schema

Here is the database schema used in this example:-

CREATE DATABASE graphtestdb;
USE graphtestdb;
CREATE TABLE sales
  (month INT PRIMARY KEY,
  productx REAL,
  producty REAL,
  productz REAL);
INSERT INTO sales VALUES(1,   345, 100, 800);
INSERT INTO sales VALUES(2,   445, 200, 740);
INSERT INTO sales VALUES(3,   945, 300, 680);
INSERT INTO sales VALUES(4,  1145, 400, 697);
INSERT INTO sales VALUES(5,   780, 450, 524);
INSERT INTO sales VALUES(6,   640, 700, 100);
INSERT INTO sales VALUES(7,   545, 800, 200);
INSERT INTO sales VALUES(8,   445, 900, 340);
INSERT INTO sales VALUES(9,   345, 800, 280);
INSERT INTO sales VALUES(10,  245, 700, 298);
INSERT INTO sales VALUES(11,  180, 650, 240);
INSERT INTO sales VALUES(12,  440, 625, 150);

back to tutorial index