Dynamic Configuration

This tutorial demonstrates how to dynamically calculate the values of configuration parameters at runtime.

For example the data and x-axis labels may need to be constructed based upon selections made be the user.

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 salesPerYear
  (salesPerYearID  INT auto_increment,
   month           INT,
   year            INT,
   productx        REAL,
   producty        REAL,
   productz        REAL,
   PRIMARY KEY  (`salesPerYearID`),
   KEY `month`  (`month`),
   KEY `year`   (`year`)
   );

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

INSERT INTO salesPerYear VALUES(0,1,2006,34500,10000,80000);
INSERT INTO salesPerYear VALUES(0,2,2006,44500,20000,74000);
INSERT INTO salesPerYear VALUES(0,3,2006,94500,30000,68000);
INSERT INTO salesPerYear VALUES(0,4,2006,114500,40000,69700);
INSERT INTO salesPerYear VALUES(0,5,2006,78000,45000,52400);
INSERT INTO salesPerYear VALUES(0,6,2006,64000,70000,10000);
INSERT INTO salesPerYear VALUES(0,7,2006,44500,30000,30000);
INSERT INTO salesPerYear VALUES(0,8,2006,54500,50000,74000);
INSERT INTO salesPerYear VALUES(0,9,2006,64500,20000,28000);
INSERT INTO salesPerYear VALUES(0,10,2006,64500,30000,63700);
INSERT INTO salesPerYear VALUES(0,11,2006,38000,55000,82400);
INSERT INTO salesPerYear VALUES(0,12,2006,74000,30000,90000);
INSERT INTO salesPerYear VALUES(0,1,2007,74500,55000,80000);
INSERT INTO salesPerYear VALUES(0,2,2007,78500,54000,84000);
INSERT INTO salesPerYear VALUES(0,3,2007,72500,53000,88000);
INSERT INTO salesPerYear VALUES(0,4,2007,73500,52000,89700);
INSERT INTO salesPerYear VALUES(0,5,2007,78000,55000,82400);
INSERT INTO salesPerYear VALUES(0,6,2007,74000,50000,80000);
INSERT INTO salesPerYear VALUES(0,7,2007,74500,51000,80000);
INSERT INTO salesPerYear VALUES(0,8,2007,77500,52000,84000);
INSERT INTO salesPerYear VALUES(0,9,2007,74500,53000,88000);
INSERT INTO salesPerYear VALUES(0,10,2007,78500,54000,89700);
INSERT INTO salesPerYear VALUES(0,11,2007,78000,55000,82400);
INSERT INTO salesPerYear VALUES(0,12,2007,74000,54000,80000);
INSERT INTO salesPerYear VALUES(0,1,2008,84500,20000,20000);
INSERT INTO salesPerYear VALUES(0,2,2008,84500,30000,24000);
INSERT INTO salesPerYear VALUES(0,3,2008,84500,35000,28000);
INSERT INTO salesPerYear VALUES(0,4,2008,84500,36000,29700);
INSERT INTO salesPerYear VALUES(0,5,2008,88000,45000,22400);
INSERT INTO salesPerYear VALUES(0,6,2008,84000,42000,20000);
INSERT INTO salesPerYear VALUES(0,7,2008,84500,44000,20000);
INSERT INTO salesPerYear VALUES(0,8,2008,84500,47000,24000);
INSERT INTO salesPerYear VALUES(0,9,2008,84500,42000,28000);
INSERT INTO salesPerYear VALUES(0,10,2008,84500,43000,29700);
INSERT INTO salesPerYear VALUES(0,11,2008,88000,45000,22400);
INSERT INTO salesPerYear VALUES(0,12,2008,84000,48000,20000);

Step 2 - Create a Custom Data Function

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

<?php
function datascript() {
        $dataLines = array();
        // ensure $_REQUEST["year"] is set and has a value
        // between 2006 and 2008.
        if (!isset($_REQUEST["year"])) {
                $_REQUEST["year"] = 2008;
        }
        if ($_REQUEST["year"]>2008 || $_REQUEST["year"]<2006) {
                $_REQUEST["year"] = 2008;
        }
        // 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 salesPerYear
                 WHERE year=".$_REQUEST["year"]."
                 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);
        // return the data lines to the graphing software
        return $dataLines;
}
// Make sure there are no characters (not even a space or return character)
// after the following line
?>

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.php' and add the following contents to it. This file should then be placed in the /graphtest/ directory.