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