This post has been long overdue. This post will show you how to pass a date parameter to a line chart.

The parameter values will show different series values (traffic count in this case) based on your date selection.

 

1. Set up chart options

Set up the chart option. Notice that there’s no hard-coded data.

options = {
chart: {
renderTo: 'container',
type: 'line'
},
title: {
text: ''
},
subtitle: {
text: ''
},
xAxis: {
categories: [],
labels: {
align: 'center',
x: -3,
y: 20,
formatter: function() {
return Highcharts.dateFormat('%l%p', Date.parse(this.value +' UTC'));
}
}
},
yAxis: {
title: {
text: ''
}
},
tooltip: {
enabled: false,
formatter: function() {
return '<b>'+ this.series.name +'</b>
'+
this.x +': '+ this.y;
}
},
plotOptions: {
line: {
dataLabels: {
enabled: true
},
enableMouseTracking: false
}
},

series: [{
type: 'line',
name: '',
data: []
}]
}

 

2. JSON output

We want the JSON output to look like this:

{
"name" : "Foot Traffic Count",
"category" : ["2013-02-01 00:00:00","2013-02-01 01:00:00","2013-02-01 02:00:00","2013-02-01 03:00:00","2013-02-01 04:00:00","2013-02-01 05:00:00","2013-02-01 06:00:00","2013-02-01 07:00:00","2013-02-01 08:00:00","2013-02-01 09:00:00","2013-02-01 10:00:00","2013-02-01 11:00:00","2013-02-01 12:00:00","2013-02-01 13:00:00","2013-02-01 14:00:00","2013-02-01 15:00:00","2013-02-01 16:00:00","2013-02-01 17:00:00","2013-02-01 18:00:00","2013-02-01 19:00:00","2013-02-01 20:00:00","2013-02-01 21:00:00","2013-02-01 22:00:00","2013-02-01 23:00:00"],
"data" : [1606,1627,1877,1387,1367,1400,1731,1773,1462,1578,1653,1609,1735,1128,1237,1616,1529,1521,1577,1011,1689,1724,1267,1514]
}

 

3. MySQL table

I included sample data with the sql script called “foot_traffic.sql”. This sample works better with more data.

The dates range from Feb 1 – Mar 31, 2013. Each row represent hour of the day.

 

4. PHP File

The logic is returns the all rows that matches the specified date.

If the date parmaeter “$_GET[“dateParam”]” is set, use that in the where clause of the sql. If not, default to Feb 1, 2013.

<!--?php $con = mysql_connect("localhost","",""); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("blog_demo", $con); if (isset($_GET["dateParam"])) { $sql = mysql_query("SELECT timestamp_value, traffic_count FROM foot_traffic WHERE timestamp_value LIKE '".$_GET["dateParam"]."%'"); } else { $sql = mysql_query("SELECT timestamp_value, traffic_count FROM foot_traffic WHERE timestamp_value LIKE '2013-02-01%'"); } $result['name'] = 'Foot Traffic Count'; while($r = mysql_fetch_array($sql)) { $datetime = $r['timestamp_value']; $result['category'][] = $datetime; $result['data'][] = $r['traffic_count']; } print json_encode($result, JSON_NUMERIC_CHECK); mysql_close($con); ?--><?php
$con = <a href="http://www.php.net/mysql_connect">mysql_connect</a>("localhost","","");

if (!$con) {
<a href="http://www.php.net/die">die</a>('Could not connect: ' . <a href="http://www.php.net/mysql_error">mysql_error</a>());
}

<a href="http://www.php.net/mysql_select_db">mysql_select_db</a>("blog_demo", $con);

if (<a href="http://www.php.net/isset">isset</a>($_GET["dateParam"])) {
$sql = <a href="http://www.php.net/mysql_query">mysql_query</a>("SELECT timestamp_value, traffic_count FROM foot_traffic WHERE timestamp_value LIKE '".$_GET["dateParam"]."%'");
} else {
$sql = <a href="http://www.php.net/mysql_query">mysql_query</a>("SELECT timestamp_value, traffic_count FROM foot_traffic WHERE timestamp_value LIKE '2013-02-01%'");
}
$result['name'] = 'Foot Traffic Count';
while($r = <a href="http://www.php.net/mysql_fetch_array">mysql_fetch_array</a>($sql)) {

$datetime = $r['timestamp_value'];
$result['category'][] = $datetime;
$result['data'][] = $r['traffic_count'];
}

print <a href="http://www.php.net/json_encode">json_encode</a>($result, JSON_NUMERIC_CHECK);

<a href="http://www.php.net/mysql_close">mysql_close</a>($con);
?>

 

6. Date Picker

I have chosen to use jQuery UI datepicker. You can use other form elements as long as you can pass the date in the expected format.

$(function() {
$( "#datepicker" ).datepicker({
dateFormat: "yy-mm-dd",
showOn: "button",
buttonImage: "calendar.gif",
buttonImageOnly: true,
onSelect: function(dateText, inst) {
$.getJSON("data.php?dateParam="+dateText, function(json){
options.xAxis.categories = json['category'];
options.series[0].name = json['name'];
options.series[0].data = json['data'];
chart = new Highcharts.Chart(options);
});
}
});
});

HTML declaration

<div class="codecolorer-container html4strict geshi">
<table cellspacing="0" cellpadding="0">
<tbody>
<tr>
<td>
<div class="html4strict codecolorer"><<a href="http://december.com/html/4/element/p.html">p</a>>Date: <<a href="http://december.com/html/4/element/input.html">input</a> type="text" id="datepicker" /></<a href="http://december.com/html/4/element/p.html">p</a>></div></td>
</tr>
</tbody>
</table>
</div>

A couple of things to notice:

– date format should look like 213-02-11
– upon selecting a date, new json request will be sent to grab the new data

 

7. Initially populating the chart with JSON data

The chart will be populated when you select a date but we also want the initial screen to show default data

$.getJSON("data.php", function(json){
options.xAxis.categories = json['category'];
options.series[0].name = json['name'];
options.series[0].data = json['data'];
chart = new Highcharts.Chart(options);
});

The only difference when the date is selected from the date picker, we pass the date value via query string. The rest remains the same.

$.getJSON("data.php?dateParam="+dateText, function(json){
options.xAxis.categories = json['category'];
options.series[0].name = json['name'];
options.series[0].data = json['data'];
chart = new Highcharts.Chart(options);
});

 

8. Put it all together

<div class="codecolorer-container php geshi">
<table cellspacing="0" cellpadding="0">
<tbody>
<tr>
<td>
<div class="php codecolorer"><!DOCTYPE HTML>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Highcharts Example</title>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js"></script>
<script type="text/javascript">
$(document).ready(function() {
var options = {
chart: {
renderTo: 'container',
type: 'line',
marginRight: 130,
marginBottom: 25
},
title: {
text: 'Revenue vs. Overhead',
x: -20 //center
},
subtitle: {
text: '',
x: -20
},
xAxis: {
categories: []
},
yAxis: {
title: {
text: 'Amount'
},
plotLines: [{
value: 0,
width: 1,
color: '#808080'
}]
},
tooltip: {
formatter: function() {
return '<b>'+ this.series.name +'</b><br/>'+
this.x +': '+ this.y;
}
},
legend: {
layout: 'vertical',
align: 'right',
verticalAlign: 'top',
x: -10,
y: 100,
borderWidth: 0
},
series: []
}

$.getJSON("data.json", function(json) {
options.xAxis.categories = json[0]['data'];
options.series[0] = json[1];
options.series[1] = json[2];
chart = new Highcharts.Chart(options);
});
});
</script>
<script src="http://code.highcharts.com/highcharts.js"></script>
<script src="http://code.highcharts.com/modules/exporting.js"></script>
</head>
<body>
<div id="container" style="min-width: 400px; height: 400px; margin: 0 auto"></div>
</body>
</html></div></td>
</tr>
</tbody>
</table>
</div>