Connecting MySQL Database to JS Chart Using PHP

When using a charting library like AnyChart, which makes visualizing data so quick and easy, often the most challenging step for beginners is loading data. I think this is largely a result of the fact that while the ways we output the data are quite similar, the input data is commonly very different. The data may differ in many ways including the number of features in the dataset, what type these features may be, the format of the data, and how the data is stored. It is this final issue which we will be addressing today.

There is a lot of coverage in the documentation of using a data object declared within the code as well as importing your data from a file using AnyChart’s data loader. But what about importing the data from an SQL database? This is an easily done but often misunderstood approach to handling and loading data for data visualizations.

Unfortunately, it is not possible to use JavaScript to query a database because JavaScript is a client side solution and querying a database requires a server side approach due to security concerns — you just can’t store database login and password in your JavaScript code. There are many workarounds for this problem including approaches involving PHP, Ajax, Node.js, and many more. Today we will focus on using PHP to handle this task for us when our data is stored in MySQL.

In this tutorial, we will be covering how to query a MySQL database using PHP. We will describe the steps involved and reveal that it is in fact not challenging at all, just different!

How to Access MySQL Database Using PHP

Our process can be broken down into two clearly defined steps. First, we will use PHP to access our MySQL database before querying the table and appending the result into a JSON object. The second step simply draws the chart in the same way you’ve been used to.

1. Create a PHP script

The first step is to create a PHP script. This can be done by using the following opening and closing tags. All code written within these tags are read by the server as PHP.

<?php
  // code goes here.
?>

2. Declare MySQL database variables

Next, we declare our MySQL database variables required to access our database. We need the username, password, host, and database name. In the code below we have added dummy information for the purpose of showing you how this is done. Please replace these strings with the necessary information for your own database.

Note that if you have set up a local database, the host name is “localhost” and often the default username is “root”.

$username = "anychart_user"; // use your username  
$password = "password";      // use your password
$host = "localhost";         // use your host name or address 
$database="local_db";        // use your database name

3. Connect to database

In the third step, we will connect to our database using the previously defined variables. We do so with the following code. The first line creates the connection to the server while the second line connects us to the correct database.

// connect to database
$server = mysql_connect($host, $username, $password);
$connection = mysql_select_db($database, $server);

4. Perform query

Once our connection is made, we can perform a query. For those of you new to using SQL, a query is effectively an enquiry to a database that returns the requested data. Below, we do a simple query that fetches all the fields and all the values from an example table. You should replace this with the appropriate query for your needs. The first 3 lines create the query string while the final line performs the query.

// perform query
// you may need to tune the query if your database is different
$myquery = "
  SELECT * FROM  `data`
  ";
$query = mysql_query($myquery);

We then add the following code to let us know if the query returns no data:

if ( ! $query ) {
  echo mysql_error();
  die;
}

5. Create data from the query

In the fifth step, we declare ‘data’ variable as an empty array before populating this array with the results of our returned query. We do this by iterating over the returned query data with a for loop.

// create data object
$data = array();

for ($x = 0; $x < mysql_num_rows($query); $x++) {
  $data[] = mysql_fetch_assoc($query);
}

6. Encode data to JSON format

Now that we have our data in an array we need to transform it into a format that can be used with AnyChart. AnyChart works with a variety of formats, including CSV, JSON, XML, and even Google Spreadsheets. This time, we will encode our data as a JSON. This is easily done with the following code:

// encode data to json format
echo json_encode($data);

7. Close connection

The final step in our PHP script is to close the connection we have with our server.

// close connection
mysql_close($server);

With everything put together we have the following PHP script:

<?php
  // declare database variables
  // change to the information relevant
  // to your database
  $username = "anychart_user";
  $password = "password";  
  $host = "localhost";
  $database="local_db";
 
  // connect to database
  $server = mysql_connect($host, $username, $password);
  $connection = mysql_select_db($database, $server);
 
  // perform query
  // change the query to one relevant to your database
  $myquery = "
    SELECT  * FROM  `data`
    ";
  $query = mysql_query($myquery);
 
  if ( ! $query ) {
    echo mysql_error();
    die;
  }
 
  // encode data to json format
  echo json_encode($data);  
 
  // close connection
  mysql_close($server);
?>

The above code connects to our MySQL database, queries it, and returns data which it then exposes in a JSON format. With our data all prepared we can get onto the fun part: JS charting!

8. Draw Chart

Once we have our data imported using PHP it is very easy to use with AnyChart. All we need to do is use the same data adapter module we’ve used for importing files. We can do this with the following script:

<script src="https://cdn.anychart.com/releases/8.8.0/js/anychart-data-adapter.min.js"></script>

Once that is done we simply refer to our PHP script when loading the data. Like this:

anychart.data.loadJsonFile("php/data.php", function (data) {
  // chart code goes here
})

And otherwise, it is business as usual with the entire HTML/CSS/JavaScript code below:

<html>
<head>
  <script src="https://cdn.anychart.com/releases/8.8.0/js/anychart-base.min.js"></script>
  <script src="https://cdn.anychart.com/releases/8.8.0/js/anychart-data-adapter.min.js"></script>
  <style type="text/css">
    html,
    body,
    #container {
      width: 100%;
      height: 100%;
      margin: 0;
      padding: 0;
    }
  </style>
</head>
<body>
  <div id="container"></div>
  <script>
    anychart.onDocumentReady(function () {
      anychart.data.loadJsonFile("php/data.php", function (data) {
        // create a chart and set loaded data
        chart = anychart.bar(data);
        chart.container("container");
        chart.draw();
      });
    });
  </script>
</body>
</html>

Conclusion

As you can see this approach to handling data for data visualizations is a lot more accessible than you would think. By following these clearly defined steps we can easily use PHP to query our database, retrieve the data as a JSON object before visualizing it using AnyChart.

The example of connecting a MySQL database to JS charts created with AnyChart described in this tutorial is pretty basic. If you are a more experienced developer and feel ready for more complicated integrations, you may also be interested in checking out PHP, Symfony and MySQL, PHP, Laravel and MySQL, PHP, Slim and MySQL, and other PHP integration templates, as well as MySQL integration templates for other languages.

Although we focused on PHP in this tutorial, it must be noted that the other approaches are viable, easy, and may be better suited to your use case. These include using WebSockets, Comet, Ajax, Node.js, and more. If you are in need of tutorials on any of these technologies please feel free to reach out to me and/or the AnyChart team and I’m sure we can help you out!

You can also add more data in JSON output and map the data for more complex charts.

• Categories: AnyChart Charting Component, AnyGantt, AnyMap, AnyStock, HTML5, JavaScript, JavaScript Chart Tutorials, Tips and Tricks
•    2 Comments »

---

Comments (2)

CAPTCHA Code *