Bind jQuery Chart to MySql Database using PHP

In this post we will show you how to connect our jQuery Chart to MySql Database using PHP. We will obtain the data from MySql Database and especially the Northwind Database. You can download the Northwind database .sql script here and run it into MySQL to create the database.

The first thing we need to do is create the file we’ll connect with. We’ll call this file connect.php.

Collapse | Copy Code

<?php
# FileName="connect.php"
$hostname = "localhost";
$database = "northwind";
$username = "root";
$password = "";
?>

Now we have our file to do the connection for us and we need to create the file that will run the query and bring the data so our Chart can be populated. We will call the file data.php.

Collapse | Copy Code

<?php
 #Include the connect.php file
 include('connect.php');
 #Connect to the database
 //connection String
 $connect = mysql_connect($hostname, $username, $password)
 or die('Could not connect: ' . mysql_error());
 //Select The database
 $bool = mysql_select_db($database, $connect);
 if ($bool === False){
    print "can't find $database";
 }

 $query = "SELECT * FROM  `invoices` ORDER BY OrderDate LIMIT 0 , 100";
 $result = mysql_query($query) or die("SQL Error 1: " . mysql_error());

 // get data and store in a json array
 while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
  $orders[] = array(
   'OrderDate' => $row['OrderDate'],
   'ProductName' => $row['ProductName'],
   'Quantity' => $row['Quantity']
    );
 }

 echo json_encode($orders);
?>

The data is returned as JSON. This is it for the connection and data gathering. Let’s see how to add the data we just gathered into our jQuery Chart. Create the index.php file and add references to the following javascript and css files.

Collapse | Copy Code

<link rel="stylesheet" href="styles/jqx.base.css" type="text/css" />
<script type="text/javascript" src="jquery-1.7.1.min.js"></script>
<script type="text/javascript" src="jqxcore.js"></script>
<script type="text/javascript" src="jqxchart.js"></script>
<script type="text/javascript" src="jqxdata.js"></script>

Create a div tag for the Chart.

Collapse | Copy Code

<div id="jqxChart"></div>

Create your Chart and load the data. We define a source object for the Chart and bind that source to the data.php which returns the JSON data. We also set up the settings object and define the chart’s categoryAxis(horizontal X axis), valueAxis(vertical Y axis) and the chart serie. For more information about the Chart’s initialization visit this help topic: jquery-chart-getting-started.htm

Collapse | Copy Code

<script type="text/javascript">
 $(document).ready(function () {
  var source =
  {
    datatype: "json",
    datafields: [
     { name: 'OrderDate', type: 'date'},
     { name: 'Quantity'},
     { name: 'ProductName'}
   ],
   url: 'data.php'
  };  

    var dataAdapter = new $.jqx.dataAdapter(source,
  {
   autoBind: true,
   async: false,
   downloadComplete: function () { },
   loadComplete: function () { },
   loadError: function () { }
  });

  // prepare jqxChart settings
  var settings = {
   title: "Orders by Date",
   showLegend: true,
   padding: { left: 5, top: 5, right: 5, bottom: 5 },
   titlePadding: { left: 90, top: 0, right: 0, bottom: 10 },
   source: dataAdapter,
   categoryAxis:
    {
     text: 'Category Axis',
     textRotationAngle: 0,
     dataField: 'OrderDate',
     formatFunction: function (value) {
      return $.jqx.dataFormat.formatdate(value, 'dd/MM/yyyy');
     },
     showTickMarks: true,
     tickMarksInterval: Math.round(dataAdapter.records.length / 6),
     tickMarksColor: '#888888',
     unitInterval: Math.round(dataAdapter.records.length / 6),
     showGridLines: true,
     gridLinesInterval: Math.round(dataAdapter.records.length / 3),
     gridLinesColor: '#888888',
     axisSize: 'auto'
    },
   colorScheme: 'scheme05',
   seriesGroups:
    [
     {
      type: 'line',
      valueAxis:
      {
       displayValueAxis: true,
       description: 'Quantity',
       //descriptionClass: 'css-class-name',
       axisSize: 'auto',
       tickMarksColor: '#888888',
       unitInterval: 20,
       minValue: 0,
       maxValue: 100
      },
      series: [
        { dataField: 'Quantity', displayText: 'Quantity' }
        ]
     }
    ]
  };

  // setup the chart
  $('#jqxChart').jqxChart(settings);
 });
</script>

The result is a nice looking jQuery Chart.

AhmedOumezzine

03:25

Read More

Using PHP to hook up charts to MySQL data base

The purpose of this article is to show you how you can hook up your client-side charts to the database servers on your back end using server-side scripting languages.
We’ll be using MySQL and PHP. However the principles outlined in this article will be applicable to any combination of server-side technologies, like ASP.NET+MSSQL, etc.
We’re gonna follow this natural progression:

1. Create and populate sample MySQL table, that we will be using for tests.
2. Create PHP scripts, that load data off the DB server and generate data suitable, for charts.
3. Implement a plain vanilla JavaScript data loader to load the data from the server.
4. Hook it all together to a working serial chart prototype.

#1: The database

For testing purposes, I’m going to create a very basic 3 column database table "my_chart_data": 1 column for category and 2 value columns. I’m also going to populate it with some random data:

CREATE TABLE IF NOT EXISTS `my_chart_data` (
  `category` date NOT NULL,
  `value1` int(11) NOT NULL,
  `value2` int(11) NOT NULL
);

Then we’ll populate with some data:

INSERT INTO `my_chart_data` (`category`, `value1`, `value2`) VALUES
('2013-08-24', 417, 127),
('2013-08-25', 417, 356),
('2013-08-26', 531, 585),
('2013-08-27', 333, 910),
('2013-08-28', 552, 30),
('2013-08-29', 492, 371),
('2013-08-30', 379, 781),
('2013-08-31', 767, 494),
('2013-09-01', 169, 364),
('2013-09-02', 314, 476),
('2013-09-03', 437, 759);

#2: Generating the data

In this step we’re going to create a PHP script that connects to MySQL server, loads the data and outputs the data in JSON format, which is a de facto standard for JavaScript-based applications.

<?php
// Connect to MySQL
$link = mysql_connect( 'localhost', 'root', '' );
if ( !$link ) {
  die( 'Could not connect: ' . mysql_error() );
}

// Select the data base
$db = mysql_select_db( 'test', $link );
if ( !$db ) {
  die ( 'Error selecting database \'test\' : ' . mysql_error() );
}

// Fetch the data
$query = "
  SELECT *
  FROM my_chart_data
  ORDER BY category ASC";
$result = mysql_query( $query );

// All good?
if ( !$result ) {
  // Nope
  $message  = 'Invalid query: ' . mysql_error() . "\n";
  $message .= 'Whole query: ' . $query;
  die( $message );
}

// Print out rows
while ( $row = mysql_fetch_assoc( $result ) ) {
  echo $row['category'] . ' | ' . $row['value1'] . ' | ' .$row['value2'] . "\n";
}

// Close the connection
mysql_close($link);
?>

This prints out the data from our table nicely:

2013-08-24 | 417 | 127
2013-08-25 | 417 | 356
2013-08-26 | 531 | 585
2013-08-27 | 333 | 910
2013-08-28 | 552 | 30
2013-08-29 | 492 | 371
2013-08-30 | 379 | 781
2013-08-31 | 767 | 494
2013-09-01 | 169 | 364
2013-09-02 | 314 | 476
2013-09-03 | 437 | 759

That’s nice, but that’s not JSON, we’re aiming for. Let’s modify our script to produce a valid JSON output:

// Print out rows
$prefix = '';
echo "[\n";
while ( $row = mysql_fetch_assoc( $result ) ) {
  echo $prefix . " {\n";
  echo '  "category": "' . $row['category'] . '",' . "\n";
  echo '  "value1": ' . $row['value1'] . ',' . "\n";
  echo '  "value2": ' . $row['value2'] . '' . "\n";
  echo " }";
  $prefix = ",\n";
}
echo "\n]";

Now it produces a nicely formatted, valid JSON data:

[{
  "category": "2013-08-24",
  "value1": 417,
  "value2": 127
}, {
  "category": "2013-08-25",
  "value1": 417,
  "value2": 356
}, {
  "category": "2013-08-26",
  "value1": 531,
  "value2": 585
}, {
  "category": "2013-08-27",
  "value1": 333,
  "value2": 910
}, {
  "category": "2013-08-28",
  "value1": 552,
  "value2": 30
}, {
  "category": "2013-08-29",
  "value1": 492,
  "value2": 371
}, {
  "category": "2013-08-30",
  "value1": 379,
  "value2": 781
}, {
  "category": "2013-08-31",
  "value1": 767,
  "value2": 494
}, {
  "category": "2013-09-01",
  "value1": 169,
  "value2": 364
}, {
  "category": "2013-09-02",
  "value1": 314,
  "value2": 476
}, {
  "category": "2013-09-03",
  "value1": 437,
  "value2": 759
}]

Let’s go and verify the output if it’s a valid JSON. Yup, it is.

#3: Loading the data in browser via JavaScript

I’m going to create data loader functions in the AmCharts namespace, so to avoid any potential conflicts with your and 3rd party JavaScript code.
Let’s create a function that first loads the target URL data then parses it. Please note that JSON is essentially JavaScript, so we’re just going to use eval() function to “parse” the data.

AmCharts.loadJSON = function(url) {
  // create the request
  if (window.XMLHttpRequest) {
    // IE7+, Firefox, Chrome, Opera, Safari
    var request = new XMLHttpRequest();
  } else {
    // code for IE6, IE5
    var request = new ActiveXObject('Microsoft.XMLHTTP');
  }

  // load it
  // the last "false" parameter ensures that our code will wait before the
  // data is loaded
  request.open('GET', url, false);
  request.send();

  // parse adn return the output
  return eval(request.responseText);
};

NOTE: Make sure this code is executed after the amcharts.js libarry is included, so the AmCharts object is already available.
Now let’s start building out the chart code by using the above function:

 AmCharts.ready(function() {
   // load the data
   var chartData = AmCharts.loadJSON('data.php');

   // this is a temporary line to verify if the data is loaded and parsed correctly
   // please note, that console.debug will work on Safari/Chrome only
   console.debug(chartData);

   // build the chart
   // ...
 });

Let’s check browser’s console:

Beautiful!
NOTE #2: Due to JavaScript security restrictions, the above function will not work if you load the page locally (via file:///). You need to have it loaded via web server (http://)

#4: Build a fully functional chart using loaded data

Here’s the full code for the chart:

var chart;

// create chart
AmCharts.ready(function() {

  // load the data
  var chartData = AmCharts.loadJSON('data.php');

  // SERIAL CHART    
  chart = new AmCharts.AmSerialChart();
  chart.pathToImages = "http://www.amcharts.com/lib/images/";
  chart.dataProvider = chartData;
  chart.categoryField = "category";
  chart.dataDateFormat = "YYYY-MM-DD";

  // GRAPHS

  var graph1 = new AmCharts.AmGraph();
  graph1.valueField = "value1";
  graph1.bullet = "round";
  graph1.bulletBorderColor = "#FFFFFF";
  graph1.bulletBorderThickness = 2;
  graph1.lineThickness = 2;
  graph1.lineAlpha = 0.5;
  chart.addGraph(graph1);

  var graph2 = new AmCharts.AmGraph();
  graph2.valueField = "value2";
  graph2.bullet = "round";
  graph2.bulletBorderColor = "#FFFFFF";
  graph2.bulletBorderThickness = 2;
  graph2.lineThickness = 2;
  graph2.lineAlpha = 0.5;
  chart.addGraph(graph2);

  // CATEGORY AXIS 
  chart.categoryAxis.parseDates = true;

  // WRITE
  chart.write("chartdiv");
});

And here’s, Ladies and Gentlemen, the final output:

If you’re not big on copying and pasting the code, you can download the complete working example with all the files used in this tutorial here: tutorial_assets_external-data.zip

AhmedOumezzine

03:22

Read More