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

Output - Dynamic Data Load on Column Chart using PHP and Google Chart API - Clue Mediator

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:

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

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.

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

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

dynamic_column_chart.php

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