In my previous post “Using Highcharts with PHP and MySQL”, the database table results were output as tab-separated values.
Since JSON is more widely used data format, it makes sense to provide sample code that uses JSON.
Additionally, this post will show Highcharts with multiple series.
1. Database table
I’ve included the sql file to create the table and populate with sample data in the code sample package.
2. JSON Data
In data.php, we want the following output:
[{ "name": "Revenue", "data": [23987, 24784, 25899, 25569, 25897, 25668, 24114, 23899, 24987, 25111, 25899, 23221] }, { "name": "Overhead", "data": [21990, 22365, 21987, 22369, 22558, 22987, 23521, 23003, 22756, 23112, 22987, 22897] }]
This requires a JSON encoded array of two arrays each containing series information. In this example, the series are Revenue and Overhead.
First, you need to get the Revenue data.
$sth = mysql_query("SELECT revenue FROM projections_sample"); $rows = array(); $rows['name'] = 'Revenue'; while($r = mysql_fetch_array($sth)) { $rows['data'][] = $r['revenue']; }
Then, grab the Overhead data.
$sth = mysql_query("SELECT overhead FROM projections_sample"); $rows1 = array(); $rows1['name'] = 'Overhead'; while($rr = mysql_fetch_assoc($sth)) { $rows1['data'][] = $rr['overhead']; }
Finally, put them in a master array and output the JSON encoded string
$result = array(); array_push($result,$rows); array_push($result,$rows1); print json_encode($result, JSON_NUMERIC_CHECK);
Full data.php code
<!--?php $con = mysql_connect("localhost","root",""); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("highcharts", $con); $sth = mysql_query("SELECT revenue FROM projections_sample"); $rows = array(); $rows['name'] = 'Revenue'; while($r = mysql_fetch_array($sth)) { $rows['data'][] = $r['revenue']; } $sth = mysql_query("SELECT overhead FROM projections_sample"); $rows1 = array(); $rows1['name'] = 'Overhead'; while($rr = mysql_fetch_assoc($sth)) { $rows1['data'][] = $rr['overhead']; } $result = array(); array_push($result,$rows); array_push($result,$rows1); print json_encode($result, JSON_NUMERIC_CHECK); mysql_close($con); ?--><?php $con = <a href="http://www.php.net/mysql_connect">mysql_connect</a>("localhost","root",""); 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>("highcharts", $con); $sth = <a href="http://www.php.net/mysql_query">mysql_query</a>("SELECT revenue FROM projections_sample"); $rows = <a href="http://www.php.net/array">array</a>(); $rows['name'] = 'Revenue'; while($r = <a href="http://www.php.net/mysql_fetch_array">mysql_fetch_array</a>($sth)) { $rows['data'][] = $r['revenue']; } $sth = <a href="http://www.php.net/mysql_query">mysql_query</a>("SELECT overhead FROM projections_sample"); $rows1 = <a href="http://www.php.net/array">array</a>(); $rows1['name'] = 'Overhead'; while($rr = <a href="http://www.php.net/mysql_fetch_assoc">mysql_fetch_assoc</a>($sth)) { $rows1['data'][] = $rr['overhead']; } $result = <a href="http://www.php.net/array">array</a>(); <a href="http://www.php.net/array_push">array_push</a>($result,$rows); <a href="http://www.php.net/array_push">array_push</a>($result,$rows1); 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); ?>
3. Presentation PHP
The part that connects Highcharts to JSON back-end data is as follows:
$(document).ready(function() { $.getJSON("data.php", function(json) { chart = new Highcharts.Chart({ series: json
Full code
<!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"> $(function () { var chart; $(document).ready(function() { $.getJSON("data.php", function(json) { chart = new Highcharts.Chart({ chart: { renderTo: 'container', type: 'line', marginRight: 130, marginBottom: 25 }, title: { text: 'Revenue vs. Overhead', x: -20 //center }, subtitle: { text: '', x: -20 }, xAxis: { categories: ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'] }, 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: json }); }); }); }); </script> </head> <body> <script src="http://code.highcharts.com/highcharts.js"></script> <script src="http://code.highcharts.com/modules/exporting.js"></script> <div id="container" style="min-width: 400px; height: 400px; margin: 0 auto"></div> </body> </html>
4. Tips and Tricks
As I was testing the code, I noticed that my numeric values had double quotes around them and Highcharts would not render the line chart.
[{ "name": "Revenue", "data": ["23987", "24784", "25899", "25569", "25897", "25668", "24114", "23899", "24987", "25111", "25899", "23221"] }, { "name": "Overhead", "data": ["21990", "22365", "21987", "22369", "22558", "22987", "23521", "23003", "22756", "23112", "22987", "22897"] }]
I tracked the problem to json_encode function. Apparently, this is a known issue in PHP and can be resolved by using the JSON_NUMERIC_CHECK flag.
json_encode($result, JSON_NUMERIC_CHECK);
hi, i succes create the chart, but how to make x axis read from my table, my data is like date=2012-11-05 20:43:01, amount=23, i want the date to be X axis and amount to be Yaxis, and make 2 data series from other table,
sorry for my english
Rivan,
This might help you as well.
http://blueflame-software.com/blog/how-to-create-dynamic-x-axis-data-using-json/
I think this will help:
http://stackoverflow.com/questions/6563997/highcharts-how-to-load-data-from-json-to-xaxis-categories-and-series-data
This requires you to parse json and extract both x-axis data and y-axis data
$.getJSON(‘loadData.php’, function(JSONresult) {
yData = options.series[0].data; //Array to store data for y column
xData = options.xAxis.categories; //Array to store data for x column
xDataObj = JSONresult[0];
yDataObj = JSONresult[1];
for(var key in xDataObj){
xData.push(xDataObj[key]);
}
for(var key in yDataObj){
yData.push(parseFloat(yDataObj[key]));
}
I am working on another example to demostrate this. I will post it once it’s ready.
Very nice tutorial.
I’ve been attempting to base my code off what you have written, but I am not certain about a couple of things.
1.) Is the .json file an actual file generated from the PHP or was it just placed in the folder as a “visual enhancement” (probably not the right term)?
2.) I am getting a blank div, so my assumptions are that #1 is the biggest culprit. A table is retrieved so it isn’t a matter of not having PHP, MySQL, and Apache hooked up properly.
Mike. The .json file doesn’t need to be generated. As long as the content of data.php contains json compatible text, you should be fine.
thank you!!!! excelent!
gonzalo – Argentina
hi, i want to make categories to be dynamic. so it will grab data from date coloumn in database. how to do that? can you give sample please
Yup. Check this post out:
http://blueflame-software.com/blog/how-to-create-dynamic-x-axis-data-using-json/
Hey great tutorials!, do u have an example similar to this using ajax?
Awesome post! I searched 2 hours for a decent tutorial till I landed on this one. Keep up to good work!
Energia
Thanks for the positive feedback. I am currently working on drill down sample that will go up in a few days.
Hello
thanks for your good work. Please can you help me? I extract two values from my Database in the browser I can see the data’s with the data.php site. But in the graph I can only see the the description from the lines but now line an values.
The seccond when I use the JSON_NUMERIC_CHECK I become the Failure : Warning: json_encode() expects exactly 1 parameter, 2 given in xxx line 36.
Thank you for your help
Hello Sir,
I have searched a lot on internet and thank you for your solution.
I have created High chart using your above tutorial.
Sir, Do you have same kind of steps for Creating HighStock graph ?
It will be great help.
I have tried to create HighStcok chart in localhost but it is not rendering.
Here’s a Highstock sample that I created recently – http://blueflame-software.com/blog/highstock-with-dynamic-data/
I don’t understand whats the use of the json file sir. could it be that you don’t use the json file rather use the data in the mysql file. the numbers in the json is alike with the mysql file isn’t it,sir?
You don’t need .json file. In this example, I want to show that that’s what data.php should output.
My coder is trying to convince me to move to .net from PHP. I have always disliked the idea because of the expenses. But he’s tryiong none the less. I’ve been using WordPress on a number of websites for about a year and am nervous about switching to another platform. I have heard good things about blogengine.net. Is there a way I can import all my wordpress content into it? Any kind of help would be greatly appreciated!
Nice tutorial, I have been looking for something like this for a while.
I don’t seem to be able to get the code to work though.
I just get the outline of the chart with no data drawn.
If I navigate to data.php I just see a white page, shouldn’t I see an output like displayed in the example data.json file?
It’s important that you get the correct output when you go to data.php. First thing I would do is make sure db host, username and password are correct. Data.php simply runs a few queries on your tables and if all the information is correct, you should be fine.
My problem is definitely with data.php, because if I copy and paste the data.json file into data.php the chart works as expected.
I have tested all the queries in mysql and they all work fine.
I’m assuming my connection details are correct, because if I purposely enter the wrong password I get the mysql connection error “Could not connect: Access denied for user ‘racecontrol’@’localhost’ (using password: YES)”
Also I get “True” returned when I add “echo @mysql_ping() ? ‘true’ : ‘false’;” to the bottom of the page.
Do you have any other suggestions I could try to solve it?
Thanks
This might be obvious but see if you can run the SQL’s SELECT revenue FROM projections_sample and SELECT overhead FROM projections_sample (separately of course) on your MySQL (using the same credentials) using client tools like SQLYog or PhpMyAdmin. If you don’t get any results, that will explain why you are not getting any results back.
A user at stack overflow helped me debug my problem in the end. JSON_NUMERIC_CHECK requires a minimum install of PHP 5.3.3 and my server host is only running 5.2.17.
The tutorial has still taught me lots though and I got there in the end, so thanks 🙂
Hey SG,
How did you manage to overcome json_numeric_check?
I have stuck with the same problem 🙁
Venki
Hey Tim,
Its pretty cool. I have followed your idea and tried to produce a spline chart.I noticed that my numeric values had double quotes around them and Highcharts would not render the line chart. Reason being I am using PHP 4[no json function supported]. Still I managed to find json_encode function and implemented and it works but JSON_NUMERIC_CHECk is not working. Any idea to do it manually?
Venk,
Sounds like you had to retrofit json_encode in php4. What I would do is to see how “int” type is being added in the source code (if column is int, json_encode does not add double quotes) and change for decimal and float to look like that.
Tim
Thanks for your Reply Tim. I will look into it and will get back to you. You are so sweet to respond!
Hi Tim,
Thanks for you real efforts. Its working now :). I appreciate it.
Venki
Hello. I just want to ask, how can i add scroll bar in the xAxis with your code? Thank you.
Brilliant examples shown, and I am slowly advancing along. The only problem I have is that IE will not refresh any changes made in the database, whereas firefox does. Is there anything I can to make this refresh dynamic please?
Thanks