Database Information File

With this method the database information file will contain the necessary information for the graphng software to connect to your database and the queries to run for each data series. At runtime the graphing software will read this file, connect to the database and execute each of the queries. It will then automatically extract the data from the result set(s) for plotting on the graph.

The database information file is a PHP script which assigns various options to a PHP array '$jpDatabase'. This file is used by the graphing software to connect to the database and execute the queries.

The array contains three sections of information:-

Database Connection Information
Data Queries
Database Abstraction Code

An example file can be found in the Sample Application directory ./jpowered/sampleApplication/dataQueries/dbConfig.php

To tell the graph to use a database information file the 'data' parameter of the IMG tag should be replaced with 'dbinfo' parameter, like this:-
<img src="http://www.domain.com/jpowered/graph/[GRAPH-STYLE]? dbinfo=[RELATIVEpathTOdbinfoFILE]& config=[URLtoCONFIG]" width="500" height="400" />
The [RELATIVEpathTOdbinfoFILE] should be a relative filesystem file path to the database information file. This file path is relative to the location of the graph software and NOT the page which contains the IMG tag.

Database Connection Information
This section contains 4 parameters which provide the graphing software with the necessary information to open a connection to the database. The parameters are:-

dbServer - database server location and port
dbUser - username the graph should use to connect to the database
dbPassword - password the graph should use to connect to the database
dbDatabase - the database name

e.g.

// Database Connection Info
$jpDatabase["dbServer"]   = "localhost:3306";
$jpDatabase["dbUser"]     = "[user]";
$jpDatabase["dbPassword"] = "[paswword]";
$jpDatabase["dbDatabase"] = "JPSAMPLEsalesDB";

Data Queries
The data query section contains the SQL the graph should issue to the database server to extract the data to be plotted. For each series of data the graph needs to know:-

The SQL Query
The name of the field(s) in the table/resultset to pick the value(s) from

For example if we have 3 series of data then the data query section would look like this:-

// Data Queries
$jpDatabase["data"] = array();

$jpDatabase["data"][0]["query"]       = "SELECT numberA FROM table1 ";
$jpDatabase["data"][0]["valueField"]  = "numberA";

$jpDatabase["data"][1]["query"]       = "SELECT numberB FROM table2 ";
$jpDatabase["data"][1]["valueField"]  = "numberB";

$jpDatabase["data"][2]["query"]       = "SELECT numberC FROM table3 ";
$jpDatabase["data"][2]["valueField"]  = "numberC";

The above example will provide the graph with a single value for each data point. However some graph styles require 2 or 3 values (e.g. the X-Y scatter graph requires 2 values and the Bubble chart requires 3 values for each data point). For these graph styles the format would be like this:-

// Data Queries
$jpDatabase["data"] = array();

$jpDatabase["data"][0]["query"]        = "SELECT numberA,numberB,numberC FROM table1 ";
$jpDatabase["data"][0]["valueXField"]  = "numberA";
$jpDatabase["data"][0]["valueYField"]  = "numberB";
$jpDatabase["data"][0]["valueZField"]  = "numberC";

$jpDatabase["data"][1["query"]        = "SELECT numberA,numberB,numberC FROM table2 ";
$jpDatabase["data"][1]["valueXField"]  = "numberA";
$jpDatabase["data"][1]["valueYField"]  = "numberB";
$jpDatabase["data"][1]["valueZField"]  = "numberC";

$jpDatabase["data"][2]["query"]        = "SELECT numberA,numberB,numberC FROM table3 ";
$jpDatabase["data"][2]["valueXField"]  = "numberA";
$jpDatabase["data"][2]["valueYField"]  = "numberB";
$jpDatabase["data"][2]["valueZField"]  = "numberC";

NOTE: All PHP Global variables ($_REQUEST, $_GET, $_POST, $_SESSION etc) are available to be used within the database information file. For instance if you wish to dynamically construct a query based on the value of "userID" held in the session then you could do something like this:-
$jpDatabase["data"][0]["query"] = "SELECT numberA,numberB,numberC FROM table1 WHERE userID=".$_SESSION["userID"];

Database Abstraction Code
This parameter tells the graphing software to use database access methods located in the file specified. The software will look for the file in the dir ./jpowered/graph/common/ Currently database methods are supplied for the MySQL database system and are located in the DBMySQL.class.php file. As such the parameter should be set like this:-
// Abstraction Class $jpDatabase["abstractionClass"] = "DBMySQL.class.php";

Putting it all together the database information file should look like this:-
// Database Connection Info $jpDatabase["dbServer"] = "localhost:3306"; $jpDatabase["dbUser"] = "[user]"; $jpDatabase["dbPassword"] = "[paswword]"; $jpDatabase["dbDatabase"] = "JPSAMPLEsalesDB"; // Data Queries $jpDatabase["data"] = array(); $jpDatabase["data"][0]["query"] = "SELECT numberA FROM table1 "; $jpDatabase["data"][0]["valueField"] = "numberA"; $jpDatabase["data"][1]["query"] = "SELECT numberB FROM table2 "; $jpDatabase["data"][1]["valueField"] = "numberB"; $jpDatabase["data"][2]["query"] = "SELECT numberC FROM table3 "; $jpDatabase["data"][2]["valueField"] = "numberC"; // Abstraction Class $jpDatabase["abstractionClass"] = "DBMySQL.class.php";

« back to Graph Data

Advanced Graphs and Charts for PHP

Database Information File

Also see:-

Documentation Contents

Database Connections