Dynamic data load on Column Chart using PHP and Google Chart API

Column charts are one of the most common and widely used chart types. It is a visual representation of data that uses bars to show the values of different categories.

Google charts API allows to create different types of charts like – pie, bar, line, column, etc. It takes Array type value as data.

In this tutorial, I show how you can create column chart using Google Chart API and load MySQL data dynamically using PHP with a live example.

Demo Download

Table of Content

Create a Table
Database Connection
HTML & PHP: Setting Up the Data and Chart Structure
JavaScript – Create column Chart
Demo
Conclusion

1. Create a Table

I am using employees table in the example. It has the following structure –

CREATE TABLE `employees` (
    `id` int(11) NOT NULL,
    `emp_name` varchar(80) NOT NULL,
    `salary` varchar(20) NOT NULL,
    `gender` varchar(10) NOT NULL,
    `city` varchar(80) NOT NULL,
    `email` varchar(80) NOT NULL
);

ALTER TABLE `events`
ADD PRIMARY KEY (`id`);

ALTER TABLE `events`
MODIFY `id` int(10) NOT NULL AUTO_INCREMENT;

2. Database Connection

Create a config.php for database configuration.

<?php

$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = ""; /* Password */
$dbname = "tutorials"; /* Database name */

$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
    die("Connection failed: " . mysqli_connect_error());
}

3. HTML & PHP: Setting Up the Data and Chart Structure

Create index.php file. This will serve as the canvas for our dynamic column chart.

Execute select query on employees table to count number of users in a city, for this I used GROUP BY city then count using count(id).

Loop on the fetched records and store data in $data Array.

Convert $data Aray in JSON format using json_encode(). The JSON data is stored within a hidden <textarea id="chartdata"> element. This hidden storage will be utilized during the chart initialization in JavaScript

Include Google Chart JS and charts.js. charts.js file is created in the next step.

<?php 
include "config.php";
?>
<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Dynamic data load on Column Chart using PHP and Google Chart API</title>
</head>
<body>
    <?php 
    // Fetch total users on the cities
    $sql = "SELECT city,count(id) as totalusers FROM employees GROUP BY city ORDER BY city";
    $records = mysqli_query($con, $sql);
    $data = array();
    while($row = mysqli_fetch_assoc($records)){
         $data[] = $row;
    }

    $jsonData = json_encode($data);
    ?>
    <!-- Store JSON data -->
    <textarea id="chartdata"><?= $jsonData ?></textarea>

    <!-- Chart -->
    <div id="cityUsersChart" style="width:100%; height:500px;"></div>

    <!-- Script -->
    <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
    <script type="text/javascript" src="chart.js"></script>
</body>
</html>

4. JavaScript – Create column Chart

Create chart.js file to initialize the Column chart.

In the drawChart() function fetch chart data from <textarea id="chartdata" > and assign it to data variable, convert it to JSON object using JSON.parse().

Create chartData Array and in the first index store header info –

var chartData = [
     ['City','Total Users',{ role: 'annotation'}],
];

Loop on the obj and read city name and total users. Store values in chartData Array, specify your data between [] – [city,totalusers,totalusers].

Directly pass the chartData to google.visualization.arrayToDataTable().

Initialize chart on cityUsersChart.

google.charts.load('current', {'packages':['corechart']});
google.charts.setOnLoadCallback(drawChart);

function drawChart() {

     var data = document.getElementById('chartdata').value;

     let obj = JSON.parse(data) ;   
     var chartData = [];

     // Add Chart data
     var chartData = [
          ['City','Total Users',{ role: 'annotation'}],
     ];

     for (var key in obj) {
          if (obj.hasOwnProperty(key)) {
               var val = obj[key];

               var city = val.city;
               var totalusers = Number(val.totalusers);

               // Add to Array
               chartData.push([city,totalusers,totalusers]);

          }
     }

     var data = google.visualization.arrayToDataTable(chartData);

     // Options 
     var options = {
          title:'City Users report',
          colors: ['#FF7F50']
     };

     var chart = new google.visualization.ColumnChart(document.getElementById('cityUsersChart'));
     chart.draw(data, options);

}

5. Demo

View Demo

6. Conclusion

Column charts are an effective way to present data in a visually appealing and easy to understand format. With PHP and MySQL you can make it dynamic.

If you are using any other chart types in your web application then you can also follow the above steps to dynamic load data.

If you found this tutorial helpful then don't forget to share.