Loading MySQL Data To Create Charts

Loading MySQL Data To Create Charts

Data Handling Overview

ZingChart's flexible data handling allows you to use data directly from a database. In this tutorial, we’re going to cover using PHP to query a MySQL database and convert the data into JavaScript variables for use in our chart.

MySQL and More

Using PHP and MySQL is a very common use case, but if you’re using a different database, we have other demos available and in development. If you’re looking for something else, try one of these demos:

• MongoDB
• CouchDB
• Firebase
• Node.js/MySQL

Now, let's get started with PHP and MySQL.

MySQL Data In Action

For this example, we have created a MySQL database containing some sample data and we would like to display this data in a JS chart. Here is the content of our t_test table:

mysql> select * from t_test;
+------+--------+--------+
| f_id | f_name | f_data |
+------+--------+--------+
|    1 | Webster|     24 |
|    2 | Parnel |     68 |
|    3 | Dena   |     48 |
|    4 | Tyrell |     70 |
|    5 | Martha |     40 |
|    6 | Summer |     15 |
|    7 | Linton |     30 |
+------+--------+--------+
7 rows in set (0.00 sec)

We must first open a connection to the database.

<?php 
/* Open connection to "zing_db" MySQL database. */
$mysqli = new mysqli("localhost", "user", "password", "zing_db");

/* Check the connection. */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}
?>

Our data exists within our t_test table, so we will query our database to receive a result set.

<?php 
/* Open connection to "zing_db" MySQL database. */
$mysqli = new mysqli("localhost", "user", "password", "zing_db");

/* Check the connection. */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

/* Fetch result set from t_test table */
$data=mysqli_query($mysqli, "SELECT * FROM t_test");
?>

To make the data compatible with our chart, we must create JavaScript objects from the data. To do this, we will use PHP while loops to loop through our data to create JavaScript arrays from our result set.

<script>
var myData=[<?php 
while($info=mysqli_fetch_array($data))
    echo $info[ 'f_data' ].','; /* We use the concatenation operator '.' to add comma delimiters after each data value. */
?>];
<?php
$data=mysqli_query($mysqli, "SELECT * FROM t_test");
?>
var myLabels=[<?php 
while($info=mysqli_fetch_array($data))
    echo '"'.$info[ 'f_name' ].'",'; /* The concatenation operator '.' is used here to create string values from our database names. */
?>];
</script>

Now that we have our database data in our JavaScript array objects, we can close the database connection.

<?php
/* Close the connection */
$mysqli->close(); 
?>

We can now use the myData and myLabels arrays just as we would with normal array objects.

window.addEventListener('load', function() {
  zingchart.render({
    id: "myChart",
    width: "100%",
    height: 400,
    data: {
      type: 'bar',
      title: {
        text: "Data Pulled from MySQL Database"
      },
      'scale-x': {
        labels: myLabels
      },
      series: [{
        values: myData
      }]
    }
  });
};

Summary

Querying a database and using the retrieved data will allow you to keep your JS chart updated with the latest data, eliminating the need to manually change the chart JSON configuration. In this example, we used a MySQL database, but the same process can be followed with any type of database. Give it a shot!