Charts are a great visual aid when presenting data. You can’t build a professional admin panel without them. They are also tricky to set up. However, there is a new library that makes things easier – xCharts. Today, we are going to use it along with the daterange picker for Twitter Bootstrap, to build a pretty, AJAX-powered chart for your web application that fetches data from a MySQL table.
The HTML
The HTML structure of the demo is pretty simple – we have to add elements on the page for the initialization of the chart, and for the date picker. As we are including bootstrap in the page anyway, we can make use of its form styling abilities and icons to make it look good.
index.php
Pretty Charts with jQuery and AJAX | Tutorialzine Demo</title> </head> </i></span> </div> </fieldset> </form> </figure> </div> </div> </script> </script> </script> </script> </script> </script> </body> </html>
We are including a good deal of external resources here. In the head section, we have the css files for xcharts, the datepicker, bootstrap (included from cloudflare’s super fast cdn), and our style.css file.
Before the closing body tag, we have the jQuery library, d3.js (required by xcharts), xcharts, the elegant sugar.js library (required by the date range plugin), the date range plugin and our script.js. In the next steps you will see how all of these work together.
The MySQL Table
As I mentioned in the intro, the script we are writing will fetch its data from a MySQL table and display it on the chart. You can find the SQL code that will create the table in schema.sql in the zip file, available for download from the buttons above. This is what the table looks like:
It has only three fields. The date field is assigned a unique index, which means that there cannot be duplicate records for the same day. The sales_ord field holds the number of sales for the day. Your database will surely differ, but as long as you return the correct JSON response from your PHP script, there won’t be any problems (more on that in the next section).
Note: Remember to enter your MySQL connection details in setup.php. You will then have to create a new MySQL database, and import schema.sql from phpMyAdmin or your management system of choice.
The PHP Code
In our PHP script, we will select the records from the table that correspond to the passed start and end date, assemble an array, and output it as a JSON:
ajax.php
header('Content-Type: application/json'); // Set up the ORM library require_once('setup.php'); if (isset($ _GET['start']) AND isset($ _GET['end'])) {
$ start = $ _GET['start'];
$ end = $ _GET['end'];
$ data = array();
// Select the results with Idiorm
$ results = ORM::for_table('chart_sales')
->where_gte('date', $ start)
->where_lte('date', $ end)
->order_by_desc('date')
->find_array();
// Build a new array with the data
foreach ($ results as $ key => $ value) {
$ data[$ key]['label'] = $ value['date'];
$ data[$ key]['value'] = $ value['sales_order'];
}
echo json_encode($ data); }
Here I am using a favorite library of mine – Idiorm. I have used it before in tutorials in the site (and in many personal projects). It is only one file (located in the lib/ folder) and makes working with databases a pure joy. All I am doing is selecting all the results from the database, which have a date value between the start and end timestamps passed with the request.
The resulting JSON response looks similar to this:
[{
"label": "2013-01-07",
"value": "4" }, {
"label": "2013-01-06",
"value": "65" }, {
"label": "2013-01-05",
"value": "96" }]
The label properties contain the MySQL date values for the respective row, and the values – the number of sales for that day. It is up to our JavaScript code to correctly handle this data and turn it into a format suitable for use with the xCharts plugin.
The JavaScript
All of our JS code lies in assets/js/script.js. The code is a bit long, and to make it easier to follow I will present it to you in chunks.
First we will declare a few variables and initialize the date range picker plugin. Notice that the date range I linked to is a fork of the original plugin. I decided to go with this version, as the original depends on date.js – a very old date/time library that conflicts with xCharts. The fork instead uses sugar.js which is a nice utility library with powerful date and time support.
assets/js/script.js
$ (function() {
// Set the default dates, uses sugarjs' methods
var startDate = Date.create().addDays(-6), // 6 days ago
endDate = Date.create(); // today
var range = $ ('#range');
// Show the dates in the range input
range.val(startDate.format('{MM}/{dd}/{yyyy}') + ' -
' + endDate.format('{MM}/{dd}/{yyyy}')); // Load chart
ajaxLoadChart(startDate,endDate);
range.daterangepicker({
startDate: startDate,
endDate: endDate,
ranges: {
'Today': ['today', 'today'],
'Yesterday': ['yesterday', 'yesterday'],
'Last 7 Days': [Date.create().addDays(-6), 'today'],
'Last 30 Days': [Date.create().addDays(-29), 'today'] // You can add more entries here
}
},function(start, end){
ajaxLoadChart(start, end);});
As you can see, we are making good use of sugar.js’ date and time methods to define the start and end point of the range. I am initializing the script with the results from the last 7 days, and updating the range input field.
Now let’s create the chart:
// The tooltip shown over the chart
var tt = $ (';').appendTo('body'),
topOffset = -32;
var data = {
"xScale" : "time",
"yScale" : "linear",
"main" : [{
className : ".stats",
"data" : [] }]
};
var opts = {
paddingLeft : 50,
paddingTop : 20,
paddingRight : 10,
axisPaddingLeft : 25,
tickHintX: 9,
// How many ticks to show horizontally
dataFormatX : function(x) {
// This turns converts the timestamps coming from
// ajax.php into a proper JavaScript Date object
return Date.create(x);
},
tickFormatX : function(x) {
// Provide formatting for the x-axis tick labels.
// This uses sugar's format method of the date object.
return x.format('{MM}/{dd}');
},
"mouseover": function (d, i) {
var pos = $ (this).offset();
tt.text(d.x.format('{Month} {ord}') + ': ' + d.y).css({
top: topOffset + pos.top,
left: pos.left
}).show();
},
"mouseout": function (x) {
tt.hide();
}
};
// Create a new xChart instance, passing the type
// of chart a data set and the options object
var chart = new xChart('line-dotted', data, '#chart' , opts);
First I define a configuration object for xCharts, with properties and callback functions. In the dataFormatX property, I am transforming the yyyy-mm-dd strings returned from the AJAX request, into proper JavaScript Date objects, so that the plugin can correctly display them and do its calculations.
I am also passing an event handler for the mouseover/mouseout plugin events, and use them to show a tooltip (the plugin doesn’t come with one out of the box).
Lastly, here is the JavaScript function for loading data with AJAX:
// Function for loading data via AJAX and showing it on the chart
function ajaxLoadChart(startDate,endDate) {
// If no data is passed (the chart was cleared)
if(!startDate || !endDate){
chart.setData({
"xScale" : "time",
"yScale" : "linear",
"main" : [{
className : ".stats",
data : [] }]
});
return;
}
// Otherwise, issue an AJAX request
$ .getJSON('ajax.php', {
start: startDate.format('{yyyy}-{MM}-{dd}'),
end:
endDate.format('{yyyy}-{MM}-{dd}')
}, function(data) {
var set = [];
$ .each(data, function() {
set.push({
x : this.label,
y : parseInt(this.value, 10)
});
});
chart.setData({
"xScale" : "time",
"yScale" : "linear",
"main" : [{
className : ".stats",
data : set }]
});
});
} });
xCharts exposes the setData method so you can easily replace the displayed data. The className attribute is important, as this is what the plugin uses to identify your chart. If you omit this property, all kinds of strange bugs will occur (trust me, I know).
With this our pretty charts are complete!
We’re done!
You can use this example to enhance your admin areas and to visualize statistical data in a beautiful interface.