Dynamic Data Load on Column Chart using PHP and Google Chart API
Visualizing data is a crucial aspect of data analysis, and column charts are an effective way to represent data in a visually appealing manner. In this blog post, we will explore how to create dynamic column charts using PHP and the Google Chart API. By leveraging the power of PHP and Google Charts, we can dynamically load data into the chart, allowing us to update the chart with new data without reloading the entire page.
Demo Application
Steps: Dynamic Data Load on Column Chart using PHP and Google Chart API
- Setup
- Create the Database Table
- Fetch Data from the Server
- Load Google Chart API and Draw the Chart
- Display the Chart
1. Setup
First, create a new PHP file (e.g., dynamic_column_chart.php) and include the necessary scripts for Google Chart API. To do this, you need to include the following script in the <head> section of your PHP file:
1 | <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script> |
2. Create the Database Table
Next, we need to create a table in the database to store the data that will be displayed in the column chart. Here’s the SQL query to create the data_table:
1 2 3 4 5 6 | CREATE TABLE data_table ( id INT(11) NOT NULL AUTO_INCREMENT, category VARCHAR(255) DEFAULT NULL, value INT(11) DEFAULT NULL, PRIMARY KEY (id) ); |
3. Fetch Data from the Server
In this step, we will write PHP code to fetch data from your desired data source, such as a MySQL database. For this example, we will assume that you have a table called data_table with two columns: category and value.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | <?php // Replace with your database connection details $dbhost = "localhost"; $dbuser = "root"; $dbpass = ""; $dbName = "demo"; // Create connection $conn = mysqli_connect($dbhost, $dbuser, $dbpass, $dbName); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } // Fetch data from the database $sql = "SELECT category, value FROM data_table"; $result = $conn->query($sql); $data = []; while ($row = $result->fetch_assoc()) { $data[] = [$row["category"], (int) $row["value"]]; } // Close the connection $conn->close(); ?> |
4. Load Google Chart API and Draw the Chart
Next, we will use the data fetched from the server to draw the dynamic column chart. We’ll use Google Chart’s Visualization API to render the chart on the web page.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | <script type="text/javascript"> // Load the Visualization API and the corechart package. google.charts.load('current', { 'packages': ['corechart'] }); // Set a callback to run when the Google Visualization API is loaded. google.charts.setOnLoadCallback(drawChart); // Callback that creates and populates a data table and draws the chart. function drawChart() { // Create the data table. var data = new google.visualization.DataTable(); data.addColumn('string', 'Category'); data.addColumn('number', 'Value'); data.addRows(<?php echo json_encode($data); ?>); // Set chart options var options = { title: 'Dynamic Column Chart', height: 400, width: 600, hAxis: { title: 'Category', }, vAxis: { title: 'Value', minValue: 0, }, legend: 'none', }; // Instantiate and draw the chart, passing in some options. var chart = new google.visualization.ColumnChart(document.getElementById('column_chart')); chart.draw(data, options); } </script> |
5. Display the Chart
Finally, let’s display the chart on the web page.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 | <?php // Replace with your database connection details $dbhost = "localhost"; $dbuser = "root"; $dbpass = ""; $dbName = "demo"; // Create connection $conn = mysqli_connect($dbhost, $dbuser, $dbpass, $dbName); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } // Fetch data from the database $sql = "SELECT category, value FROM data_table"; $result = $conn->query($sql); $data = []; while ($row = $result->fetch_assoc()) { $data[] = [$row["category"], (int) $row["value"]]; } // Close the connection $conn->close(); ?> <!DOCTYPE html> <html> <head> <title>Dynamic Column Chart using PHP and Google Chart API</title> <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script> </head> <body> <div id="column_chart"></div> <script type="text/javascript"> // Load the Visualization API and the corechart package. google.charts.load('current', { 'packages': ['corechart'] }); // Set a callback to run when the Google Visualization API is loaded. google.charts.setOnLoadCallback(drawChart); // Callback that creates and populates a data table and draws the chart. function drawChart() { // Create the data table. var data = new google.visualization.DataTable(); data.addColumn('string', 'Category'); data.addColumn('number', 'Value'); data.addRows(<?php echo json_encode($data); ?>); // Set chart options var options = { title: 'Dynamic Column Chart', height: 400, width: 600, hAxis: { title: 'Category', }, vAxis: { title: 'Value', minValue: 0, }, legend: 'none', }; // Instantiate and draw the chart, passing in some options. var chart = new google.visualization.ColumnChart(document.getElementById('column_chart')); chart.draw(data, options); } </script> </body> </html> |
