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 structure
Database Configuration
HTML & PHP
JavaScript – Create column Chart
Demo
Conclusion

1. Table structure

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 Configuration

Create a config.php for database configuration.

Completed Code

<?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

Create index.php file.

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() and store it in <textarea id="chartdata" >. This stored value use while chart initialization in JavaScript.

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

Completed Code

<?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.

Completed Code

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.

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

Contents

1. Table structure

2. Database Configuration

3. HTML & PHP

4. JavaScript – Create column Chart

5. Demo

6. Conclusion

Leave a Comment Cancel reply