PHP Data Engine

Describes connecting to server side databases using the PHP MySQL DataEngine.

Creating your first PHP MySQL powered Chart

The first step is to make a new *.php file.
At the top of this file, please add the following code:

<?php
include "Includes/DataEngine.php";
$de = new DataEngine();
$de->sqlStatement = 'SELECT DatePeriod,Purchases,Taxes,Supplies,Rent FROM AreaData';
$de->dataFields = 'xAxis=DatePeriod,yAxis=Purchases,yAxis=Taxes,yAxis=Supplies,yAxis=Rent';
$series = $de->getSeries();
?>

The JS section will define options for the chart.

var chartConfig = {
  targetElement: "cc",
  defaultSeries: { type: "area", opacity: 0.85 }
};

The PHP series data is then added to the chart config options in the JS section..

var php_var;
if(php_var =<?php echo json_encode($series); ?>)
{
chartConfig.series =JSON.parse(php_var);
}

Finally, the chart is initialized with the configuration which include the data from the database.

chart = new JSC.Chart(chartConfig);

Connecting to Data

The JSCharting PHP bundle provides a DataEngine object which can be used to automatically obtain data from any MySQL data source. MySQL's settings are set in the php/Includes/MySQLConnection.php file; please update it with your database settings. The DataEngine manipulates a serialized string of series collection objects which contains data that is consumed by the chart.

DataEngine

The DataEngine object is instantiated with:

$de = new DataEngine();

DataEngine Properties

sqlStatement

The SQL statement used to obtain the charting data.

$de->sqlStatement = 'SELECT DatePeriod,Purchases,Taxes,Supplies,Rent FROM AreaData';

dataFields

Data fields specify the 'database column' to 'element value' relationships. It enables mapping which data table column or query populates a given point property.

Basic Syntax

Escape Character

Some database columns can have characters such as commas ',' in their names. In order to reference these column names an escape character can be used. 'YAxis=Tom\,Harry'

Supported tokens if calling getSeries() are:

DataFields Tokens

Token	Description
'name'	Name of the point assigned to the Point.name property.
'xAxis'	X value of the point assigned to the Point.x property.
'yAxis'	Y value of the point assigned to the Point.y property.
'zAxis'	Z value of the point assigned to the Point.z property.
'Open'	Open value of the point assigned to the Point.open property.
'High'	High value of the point assigned to the Point.high property.
'Low'	Low value of the point assigned to the Point.low property.
'Close'	Close value of the point assigned to the Point.close property.
'ToolTip'	ToolTip value of the point assigned to the Point.tooltip property.
'Volume'	Finance Volume value for the point.

Any other fields are populated as custom attributes, for more information see 'Custom element attributes' later in this document.

$de->dataFields = 'xAxis=DatePeriod,yAxis=Purchases';

Multiple Series

My specifying multiple yAxis tokens in the dataFields property, the data column of each instance is associated with a separate series as in the following example:

$de->dataFields = 'xAxis=DatePeriod,yAxis=Purchases,yAxis=Taxes,yAxis=Supplies,yAxis=Rent';

startDate and endDate

The starting and ending dates used with sqlStatement to query filtered data from a database, the '#StartDate#' and '#StartDate#' tokens in sqlStatement are replaced with the value set to these property.

$de -> startDate = new DateTime('2014-1-1');
$de -> endDate = new DateTime('2014-12-31 23:59:59');
$de -> sqlStatement = 'SELECT MONTH(OrderDate) AS Month, SUM(1) AS Orders FROM Orders WHERE OrderDate >= #StartDate# And OrderDate <= #EndDate#;';

dateGrouping

This feature controls how the values of a given series are grouped by date. In order to use this option the first column returned by the SqlStatement must be a date/time data type.

//This setting shows all the months in the year regardless of having data for that month or not.
$de - > dateGrouping = 'Year';

Setting	Description
'Year'	Shows 12 months of a year regardless if you have data for all 12 months or not.
'Month'	Shows all the days of the month regardless if there are values for all the included days or not.
'Day'	Shows each of the 24 hours in a day regardless if values are included for each hour in the return data.

storedProcedure and addParameter

The storedProcedure property specifies a predefined stored procedure to use and the addParameter function adds parameters for the stored procedure.

$de->storedProcedure = 'spSalesDateGroup';
$de->addParameter($startDate);

Custom element attributes

Additional information stored in your database can be extracted and used in your chart to provide further information in a tooltip, element's label, within the element's hot spot URL, and any other related text strings.

Extracting from a database

Extracting attributes from a database can be accomplished with a single line of code using the dataFields property of a DataEngine object.
Assume the following database columns:

• ID
• Name
• Department
• Salary
• Location
• Phone
• Picture

Using this database we will create a chart that shows each employee's name on the X axis and there average performance on the Y axis. When we mouse over a column in the chart we want to see the employee's ID, department, and phone number. When we click a given column we want to send the employee an email.

The first step is to specify the dataFields property.

$de = new DataEngine();
$de->sqlStatement = "SELECT id,name,salary,Location,phone,Picture FROM Employees";
$de->dataFields = 'xAxis=name,yAxis=id,Location=location,phone,Picture=img';

The next step is to specify a template for the element's tooltip:

defaultPoint_tooltip: 'Location:%Location<br/>Phone:%phone<br/><img height="64" src="images/%img" width="64">',

This complex and highly functional chart is now ready. For a working sample, see Custom Attributes DB Sample

Please see the below tutorial for more information on custom attributes and client side usage.

DataEngine Methods

getSeries()

Returns a serialized string of series collection objects which contains data that is consumed by the chart.

$series = $de->getSeries();