PHP Data Engine

Chart Tutorials  

PHP Data Engine

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

PHP Database samples are available in the bundle samples/php folder. These samples can be searched using the Sample Explorer, however, PHP must be enabled in order to be visible in the Sample Explorer.

🔗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 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.

To verify the MySQL database connection and setup, update settings in the php/Includes/TestConnection.php file and run it in a browser.

🔗DataEngine

The DataEngine object is instantiated with:

$de = new DataEngine();

🔗DataEngine Properties

sqlStatement

The SQL statement used to obtain the charting data. If dataFields is not set, calling getSeries() uses the first column returned for the X values, and the second column for the Y values.

$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

"[Token]=[Data Column],..."

🔗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

TokenDescription
'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';

SettingDescription
'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.


Custom Attributes Tutorial Using custom point attributes.

🔗DataEngine Methods

getSeries()

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

If dataFields is not set, the first column returned is used for the X values, and the second for the Y values.

$series = $de->getSeries();

 

Please contact info@JSCharting.com if you wish to utilize the DataEngine functionality outside of PHP and what your server side preference is.