Using Highcharts with PHP and MySQL

I like Highcharts. There are many JavaScript-based charting components out there but I particularly enjoyed using this JavaScript library.

In this post, I want to step you through how to use Highcharts with PHP and MySQL.

1. Download Highcharts and jQuery libraries.

I prefer jQuery as the prerequisite library but it supports Prototype and Mootools as well.
Highcharts - http://www.highcharts.com/download
jQuery - http://docs.jquery.com/Downloading_jQuery

At this point, I usually create a /js folder under my project folder. In this folder, you want to include jQuery js file, Highcharts js file and Highcharts themes folder.

 

Once you have the libraries, you can create the presentation php

2. Create a presentation php

Let’s call this index.php. First, add references to JavaScript files

1
2
3
<script type="text/javascript" src="js/jquery-1.7.1.min.js" ></script>
<script type="text/javascript" src="js/highcharts.js" ></script>
<script type="text/javascript" src="js/themes/gray.js"></script>

Then, include Highcharts definitions

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
<script type="text/javascript">
    var chart;
            $(document).ready(function() {
                var options = {
                    chart: {
                        renderTo: 'container',
                        defaultSeriesType: 'line',
                        marginRight: 130,
                        marginBottom: 25
                    },
                    title: {
                        text: 'Hourly Visits',
                        x: -20 //center
                    },
                    subtitle: {
                        text: '',
                        x: -20
                    },
                    xAxis: {
                        type: 'datetime',
                        tickInterval: 3600 * 1000, // one hour
                        tickWidth: 0,
                        gridLineWidth: 1,
                        labels: {
                            align: 'center',
                            x: -3,
                            y: 20,
                            formatter: function() {
                                return Highcharts.dateFormat('%l%p', this.value);
                            }
                        }
                    },
                    yAxis: {
                        title: {
                            text: 'Visits'
                        },
                        plotLines: [{
                            value: 0,
                            width: 1,
                            color: '#808080'
                        }]
                    },
                    tooltip: {
                        formatter: function() {
                                return Highcharts.dateFormat('%l%p', this.x-(1000*3600)) +'-'+ Highcharts.dateFormat('%l%p', this.x) +': <b>'+ this.y + '</b>';
                        }
                    },
                    legend: {
                        layout: 'vertical',
                        align: 'right',
                        verticalAlign: 'top',
                        x: -10,
                        y: 100,
                        borderWidth: 0
                    },
                    series: [{
                        name: 'Count'
                    }]
                }
                // Load data asynchronously using jQuery. On success, add the data
                // to the options and initiate the chart.
                // This data is obtained by exporting a GA custom report to TSV.
                // http://api.jquery.com/jQuery.get/
                jQuery.get('data.php', null, function(tsv) {
                    var lines = [];
                    traffic = [];
                    try {
                        // split the data return into lines and parse them
                        tsv = tsv.split(/\n/g);
                        jQuery.each(tsv, function(i, line) {
                            line = line.split(/\t/);
                            date = Date.parse(line[0] +' UTC');
                            traffic.push([
                                date,
                                parseInt(line[1].replace(',', ''), 10)
                            ]);
                        });
                    } catch (e) {  }
                    options.series[0].data = traffic;
                    chart = new Highcharts.Chart(options);
                });
            });
</script>

3. Create tables and populate with data

Use your favorite MySQL editor like PhpMyAdmin or SQLyog to run the sample data sql script. If you already have data in your database, take note what columns you want to connect Highcharts to.

My tables looks something like this:

4. Create a data php

Now create data.php. This page will read the table data and display it as tab separated values.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<?php
$con = mysql_connect("localhost","user","");

if (!$con) {
  die('Could not connect: ' . mysql_error());
}

mysql_select_db("highcharts", $con);

$result = mysql_query("SELECT * FROM highcharts_php");

while($row = mysql_fetch_array($result)) {
  echo $row['timespan'] . "\t" . $row['visits']. "\n";
}

mysql_close($con);
?>

The result should look like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Friday, July 1, 2011 00:00:00 1274
Friday, July 1, 2011 01:00:00 1288
Friday, July 1, 2011 02:00:00 1275
Friday, July 1, 2011 03:00:00 1270
Friday, July 1, 2011 04:00:00 1279
Friday, July 1, 2011 05:00:00 1268
Friday, July 1, 2011 06:00:00 1267
Friday, July 1, 2011 07:00:00 1271
Friday, July 1, 2011 08:00:00 1287
Friday, July 1, 2011 09:00:00 1276
Friday, July 1, 2011 10:00:00 1278
Friday, July 1, 2011 11:00:00 1272
Friday, July 1, 2011 12:00:00 1288
Friday, July 1, 2011 13:00:00 1264
Friday, July 1, 2011 14:00:00 1280
Friday, July 1, 2011 15:00:00 1277
Friday, July 1, 2011 16:00:00 1278
Friday, July 1, 2011 17:00:00 1279
Friday, July 1, 2011 18:00:00 1277
Friday, July 1, 2011 19:00:00 1270
Friday, July 1, 2011 20:00:00 1264
Friday, July 1, 2011 21:00:00 1278
Friday, July 1, 2011 22:00:00 1284
Friday, July 1, 2011 23:00:00 1272

You can generate the same results using web frame such as CodeIgniter or CakePHP. I am showing you the most basic way to generate data from MySQL.

5. Putting it all together

Here’s how to connect Highcharts with data.php. In index.php, edit the following sections of the code to point to data.php:

1
2
3
jQuery.get('data.php', null, function(tsv) {
var lines = [];
traffic = [];

Voila!

You can download the source code from this tutorial in zip format (code, MySQL sql script and Highchart and jQuery javascripts) and also view live demo of this example.

Next time, I will show you how you can pass parameter values to make the chart more interactive.

UPDATE: another way of grabbing data from database is to use JSON. Full example here.

Comments

  1. Hi, First I want to say it is a great tutorial.
    I have a question that I hope you can answer.
    My data in my table looks like this:
    INT, DATETIME, BIGINT
    1 2012-02-28 16:56:54 17280
    The reason for the DATETIME is that I am planning to to be able to sort on HOUR/DAY/WEEK/MONTH/YEAR
    Can you please help me with a example for this ?

    Thanks in advance.
    /Kim

    • RoloHallenbeck says:

      I had the same problem. My solution:
      First: convert the DATETIME to Unix Timestamp.
      Second: format the Unix Timestamp to have the same format as: Friday, July 1, 2011 09:00:00

      while($row = mysql_fetch_array($result)) {
      $uts=strtotime($row[$valor1]); //convertir a Unix Timestamp
      $date=date(“l, F j y H:i:s”,$uts);
      //echo $valor3 . “\t” . $row[$valor2]. “\n”;
      echo $date . “\t” . $row[$valor2]. “\n”;

      }

      valor1=”Date” or “DateTime” or the name in your DataBase.
      valor2=”visits”.

      I hope it helps.

  2. I believe what you published made a bunch of sense. However, what about this? suppose you typed a catchier title? I am not saying your content is not good., however suppose you added something that grabbed a person’s attention? I mean Blue Flame Software | Let’s get it done! is a little plain. You should peek at Yahoo’s front page and watch how they create article titles to grab people to click. You might add a related video or a picture or two to grab readers interested about what you’ve got to say. In my opinion, it could bring your posts a little bit more interesting.

  3. It’s great. 2 thumb up
    I’ll looking forward the way of filtering data from MySql to HighChart
    example , choose the desire month between month from drop down.

    or selection from radio .

    if you have a source please advice ..

    Foonmod

  4. Cesar Lopes says:

    Hi,

    Your script help me a lot, but I have a doubt if my data is not a interger but a float number how can I show this result, I tried some changes but I can’t do that.

  5. Thank you for a great tutorial.

  6. ANONYMOUS says:

    Fabulous! Thank you

  7. Subterana says:

    Great example! Could you post one for a pie chart?

  8. fantastic and well written blog post to something other have completley hashed up

  9. subhashish ganguly says:

    thnx……nice tutorial

  10. Hi, I’m creating a MVC module for Joomla 2.5 and I can’t execute this little code.

    It seems that the script that are storage in the data.php file, the Joomla don’t work.

    But this code works fine in a traditional php code.

    Some can put this code work in Joomla 2.5?

  11. Everything works well for me . But i’ve only the graph displayed i cant visualize the plot and the lines !

    • I would have to see the code or live example to figure out what’s wrong with it. What happens if you use this sample without making any changes to it?

      • I’ve not made any changes !!

        Index.php

        Using Highcharts with PHP and MySQL

        var chart;
        $(document).ready(function() {
        var options = {
        chart: {
        renderTo: ‘container’,
        defaultSeriesType: ‘line’,
        marginRight: 130,
        marginBottom: 25
        },
        title: {
        text: ‘Hourly Visits’,
        x: -20 //center
        },
        subtitle: {
        text: ”,
        x: -20
        },
        xAxis: {
        type: ‘datetime’,
        tickInterval: 3600 * 1000, // one hour
        tickWidth: 0,
        gridLineWidth: 1,
        labels: {
        align: ‘center’,
        x: -3,
        y: 20,
        formatter: function() {
        return Highcharts.dateFormat(‘%l%p’, this.value);
        }
        }
        },
        yAxis: {
        title: {
        text: ‘Visits’
        },
        plotLines: [{
        value: 0,
        width: 1,
        color: '#808080'
        }]
        },
        tooltip: {
        formatter: function() {
        return Highcharts.dateFormat(‘%l%p’, this.x-(1000*3600)) +’-'+ Highcharts.dateFormat(‘%l%p’, this.x) +’: ‘+ this.y + ‘‘;
        }
        },
        legend: {
        layout: ‘vertical’,
        align: ‘right’,
        verticalAlign: ‘top’,
        x: -10,
        y: 100,
        borderWidth: 0
        },
        series: [{
        name: 'Count'
        }]
        }
        // Load data asynchronously using jQuery. On success, add the data
        // to the options and initiate the chart.
        // This data is obtained by exporting a GA custom report to TSV.
        // http://api.jquery.com/jQuery.get/
        jQuery.get(‘data.php’, null, function(tsv) {
        var lines = [];
        traffic = [];
        try {
        // split the data return into lines and parse them
        tsv = tsv.split(/\n/g);
        jQuery.each(tsv, function(i, line) {
        line = line.split(/\t/);
        date = Date.parse(line[0] +’ UTC’);
        traffic.push([
        date,
        parseInt(line[1].replace(‘,’, ”), 10)
        ]);
        });
        } catch (e) { }
        options.series[0].data = traffic;
        chart = new Highcharts.Chart(options);
        });
        });

  12. hi, how to make two series data from mysql?

  13. Nice tutorial. One of the best I have seen. Clear, consice and describes each component clearly without getting bogged down in details.

  14. Hi,
    Thanks for great tutorial!

    I integrate your script to my site, and I on the same page is displayed for 3 graphics. But there is a problem of the graphics are not rendered in the browser Firefox, in other there no problems, I can not understand what could be the problem.

    • Dmitry,

      Not sure what your exact issue is but I tried my script in Firefox and there was no problem. Here’s what I would try:

      1. Take out 2 other charts and see if it’s still happening. If it works with one, add another. If that breaks, you know that 2nd chart is the problem. Try to isolate the issue to a single chart.

      2. If you have identify the problem with one of the charts, simply check tsv data. You may have to escape special characters in your data. Use Firebug to check for any Javascript errors.

      3. Make sure there are no conflicts in your chart options or declarations between 3 charts.

  15. Thanks Tim

    You can can you please check this link http://cart.hostpro.ru/charts/test.php in different browser

    Thanks

    • I’ll just resolve the problem :)

      Error is here:

      while($row = mysql_fetch_array($result)) {
      $uts=strtotime($row[$valor1]); //convertir a Unix Timestamp
      $date=date(“l, F j y H:i:s”,$uts); <– year must be Y upper than FF and IE work's fine :)
      echo $date . “\t” . $row[$valor2]. “\n”;

      }

      • I am glad that you figured it out.

        I looked into it too and in Firefox, the following line returns NaN for the date without full year and I think that’s why the chart wouldn’t render.

        Date.parse(line[0] +’ UTC’);

  16. I like this post, enjoyed this one thankyou for posting .

  17. Excelente trabajo!!! Muchas gracias
    Gonzalo – Argentina

  18. abraham says:

    Hello, I’m trying to run the example, but I would like to modify it so that instead of dates only handle text, I want to remove the function to create the date format but when I change it does not work or appears “undefined”, unfortunately my knowledge jQuery is very basic, I wonder if you can help explaining this feature, thank you very much :)

    • abraham,

      Simple answer is remove all date format wrapper.

      From “return Highcharts.dateFormat(‘%l%p’, this.value);” to “return this.value”

      Hope this helps

  19. Hey
    thank you so much for this tutorial , it’s very helpful for me .
    i don’t know much about javascript and i need your help guys with this :
    my data is like this :
    date : temp

    2013-05-08 20:17:26 : -22

    2013-05-08 20:18:26 : -21

    2013-05-08 20:24:26 : -22

    2013-05-08 20:37:26 : -20

    2013-05-08 20:40:26 : -22

    2013-05-08 20:47:26 : -21

    2013-05-08 20:52:26 : -20

    2013-05-08 20:53:26 : -19
    how to plot it ? :)

  20. Simply wish to say your article is as astonishing. The clarity in your post is just nice
    and i could assume you’re an expert on this subject. Fine with your permission let me to grab your RSS feed to keep up to date with forthcoming post. Thanks a million and please continue the rewarding work.

  21. could you help me to give the example making stacked column with data from mysql using highchart ?

  22. Thanks for this tutorial.
    i need your help :
    My data in my table looks like this:
    date from
    24-06-2013 11:45am
    25-06-2013 12:45pm
    26-06-2013 01:15pm
    27-06-2013 02:45pm
    28-06-2013 05:00pm
    how can i plot the graph?
    thanks in advance.

  23. Verry good topic !

    I have one question (im a newbie)

    Where can i put the data file (Folder ?)
    Does it have a specific name ?
    Whoch format should it have (csv txt etc.)

  24. lelong09 says:

    Thank you for a great tutorial! useful.

  25. Hai,
    Nice tutorial.
    Only I don’t get anygarphics
    I followed your intruction and put everything under /var/www on my raspberry pi
    What I get is pure text.
    I must overseen something (very stuped).
    Any advise is welcome.
    thanks

  26. Hi Tim,
    Thanks for the tutorial. I retrieve date from Mysql and it did show twice in x-axis. May I know how to solve it?

  27. I’m really enjoying the design and layout of your website.

    It’s a very easy on the eyes which makes it much more pleasant for me to come here and visit more often.

    Did you hire out a designer to create your theme? Outstanding work!

  28. what if the data per date, not per hour. is there a setting changed?

    thx :)

Trackbacks

  1. [...] trying to display data from MYSQL. I saw an example at http://www.blueflame-software.com/blog/using-highcharts-with-php-and-mysql/. How do I implement jQuery.get for my chart? Please I’m very new to jQuery and need lots of [...]