Data Interface Script

This tutorial shows how to create a dynamic data interface script. The script can be written in any server side language and designed to connect to virtually any data source.

For the purposes of this tutorial we will be using PHP and connecting to MySQL database. At runtime the graphing software will make a URL call to the interface script which in turn will return the data.

Prerequisites

The following tutorials assume you have already downloaded and installed the
PHP Advanced Graphs and Charts package.

The latest release can be downloaded here

Directory Structure

In this tutorial you will be create a web page and a couple of configuration files. For the purposes of this tutorial you should create a directory /graphtest/ directory under your web root directory.

This tutorial also assumes that you installed the graphing software directly under the web root
(e.g. /webRootFilePath/jpowered/graph/ )

Step 1 - Create a test MySQL Database

For the purpose of this tutorial the following MySQL database should be created.-

CREATE DATABASE graphtestdb;
USE graphtestdb;
CREATE TABLE sales
  (month INT PRIMARY KEY,
  productx REAL,
  producty REAL,
  productz REAL);

Now add the following data to the 'sales' table:-

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);

Step 2 - Create a Custom Data Interface Script

Create a file named dataInterface.php which contains the following:-

<?php

$dataLines = array();

// connect to the database
$dbLink = mysql_connect("localhost:3306",
                        [USER],
                        [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);
}

// Contstruct the data lines for ProductX, ProductY and Product Z
$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) ) {
        $dataLines[] = "data".$dataNum."series1: ".$row["productx"];
        $dataLines[] = "data".$dataNum."series2: ".$row["producty"];
        $dataLines[] = "data".$dataNum."series3: ".$row["productz"];
        $dataNum++;
}

// release the result set and close the databse connection
mysql_free_result($result);
mysql_close($dbLink);

// output the data to the graphing software
for ($i=0; $i < count($dataLines); $i++) {
        print $dataLines[$i]."\n";
}


// all finished so exit
exit(0);

?>

Replace [USER] and [PASSWORD] with valid logon details for your MySQL server.

Place the file on your web server in a directory /graphtest/ under your web root directory

Note: The above assumes that your MySQL server is running on the same machine as your Web Server. If this is not the case then you will need to replace "localhost:3306" with the correct route to your MySQL server.

Step 3 - Create the config file

Create a file named 'config.txt' and add the following contents to it. This file should then be placed in the /graphtest/ directory.

width:             600
height:            400

quality:           very high

backgroundcolor:   white
grid:              true
axis:              true
3d:                true
ndecplaces:        0
thousandseparator: ,
barspacing:        5
barwidth:          70
displaybarvalues:  true
gradientfill:      true

series1:           #BB0000|Product X|left|8|triangle|true|solid|
series2:           #0000BB|Product Y|left|8|box|true|solid|
series3:           #FF8800|Product Z|left|8|diamond|true|solid|

gridbgcolor:       #FFFFCC
axiscolor:         dark grey
floorcolor:        light gray
gridcolor:         grey
gridstyle:         dotted

ylables:           true
yfont:             arial
yfontsize:         11
yfontbold:         bold
yfontitalic:       false
ylabelcolor:       black
ylabelpre:         $
ylabelpost:

titletext:         Product Sales by Month
titlefont:         arial
titlefontsize:     14
titlefontbold:     bold
titlefontitalic:   false
titlecolor:        #8080B0
titleposition:     -1,55

xtitletext:        Month
xtitlefont:        arial
xtitlefontsize:    11
xtitlefontbold:    bold
xtitlefontitalic:  false
xtitlecolor:       #8080B0
xtitleposition:    255,-1

ytitletext:        Value
ytitlefont:        arial
ytitlefontsize:    11
ytitlefontbold:    bold
ytitlefontitalic:  false
ytitlecolor:       #8080B0
ytitleposition:    -1,230

xlabels:           January|February|March|April|May|June|
xlabelorientation: Up angle
xlabeloffset:      0
xlabelcolor:       #8080B0
xlabelfont:        arial
xlabelfontsize:    10
xlabelfontbold:    false
xlabelfontitalic:  false

legend:            true
legendstyle:       horizontal
legendbgcolor:     #EEEEEE
legendbordercolor: #CCCCFF
legendtextcolor:   #8080B0
legendtitle:
legendfont:        arial
legendfontsize:    11
legendfontbold:    false
legendfontitalic:  false
legendposition:    -1,64

// for a full explanation of these parameters see:-
// http://www.jpowered.com/php-scripts/adv-graph-chart/documentation/configurationOptions.htm

Note: This config.txt file contains the parameters which determine certain aspects of the graph like colors and fonts. For the function and options of each parameter see the following page:-
Graph Parameters »

Step 4 - Create a web page and add the Graph to it

Add this HTML code to your page (replacing [www.YourDomain.com] with the actual domain of your web site and [URL FilePath] with the url of the web directory where your page resides)

<img src="http://[www.YourDomain.com]/jpowered/graph/stacked-vertical-cylinder-graph.php?
data=[URL FilePath]/graphtest/dataInterface.php&
config=[URL FilePath]/graphtest/config.txt"
width=600
height=400 />

Save the web page and load it up to your web server placing it in the /graphtest/ directory.

Final Step - View the page in a browser

Open your web browser and view the page. You should see a Stacked Vertical Cylinder Graph with 6 bars plotted.

Graphing Tutorials - Data Interface Script

Data Interface Script

Prerequisites

Directory Structure

Step 1 - Create a test MySQL Database

Step 2 - Create a Custom Data Interface Script

Step 3 - Create the config file

Step 4 - Create a web page and add the Graph to it

Final Step - View the page in a browser

Tutorials

More

Documentation

Database Connections

Installation Help