Table Chart by using MySQL database table with paging by google charts

Create table rows from a database query safely

Fetch only the required columns, encode them as JSON and populate the visualization table on the client.

<?php
$stmt = $pdo->query('SELECT name, mark FROM student ORDER BY name');
$rows = $stmt->fetchAll(PDO::FETCH_NUM);
?>
<script>
const rows = <?php echo json_encode($rows, JSON_HEX_TAG | JSON_HEX_AMP | JSON_HEX_APOS | JSON_HEX_QUOT); ?>;
</script>

Current practice: database examples should use PDO or MySQLi, and PHP arrays should be transferred to JavaScript with json_encode() instead of hand-built JavaScript strings.

Current example first; the original chart examples below are retained and cleaned up for continued learning.

Using google chart library table package we can display tabular data. Now we will use MySQL table as data source and display the records using google charts.

Collecting data from MySQL

Download the zip file at the end of this page with all source codes and to create your table with sample data.

File Name	Details
config.php	MySQLi Database connection details are stored here.
sql_dump.txt	SQL Dump to create student table with sample data.
readme.txt	Instructions on how to run the script
index.php	The main file to display records and the table chart.

Using PDO : PHP Data Object

File Name	Details
config-pdo.php	PDO Database connection details are stored here.
index-pdo.php	Using PDO the main file to display table Chart.

Using CSV (Comma Separated Value) file :

File Name	Details
index-csv.php	Reading CSV file by using fgetcsv() and display chart.
student.csv	Comma separated value ( csv ) file with data.

Download Zip file at end of this tutorial.

Table Chart with data from MySQL database table using PHP pdo and google chart library

1 : Collecting data from MySQL database

After connecting to database through config.php file, we will run the SQL to collect the data from MySQL table. You can read more on MySQLi and how to collect data from table

require "config.php";// Database connection
$query="SELECT id, name,class,mark,gender FROM student";
if($stmt = $connection->query("$query")){

  echo "No of records : ".$stmt->num_rows."<br>";
$php_data_array = Array(); // create PHP array
  echo "<table>
<tr> <th>id</th><th>name</th><th>class</th><th>mark</th><th>gender</th></tr>";
while ($row = $stmt->fetch_row()) {
   echo "<tr><td>$row[0]</td><td>$row[1]</td><td>$row[2]</td><td>$row[3]</td><td>$row[4]</td></tr>";
   $php_data_array[] = $row; // Adding to array
   }
echo "</table>";

echo "<script>
var my_2d=".json_encode($php_data_array)."
</script>";
}else{
echo $connection->error;
}

Using PDO : PHP Data Object we can connect to MySQL and retrieve the data to create the array $php_data_array.

Any one method either MySQLi ( Above code ) or PDO ( Below code ) is to be used to collect data and create the JavaScript array.
Both scripts are avaialble inside zip file at the end of this page.

require "config-pdo.php";// Database connection
$query="SELECT id, name,class,mark,gender FROM student";
$step=$dbo->prepare($query);
if($step->execute()){
$php_data_array=$step->fetchAll();
echo "<script>
var my_2d=".json_encode($php_data_array)."
</script>";
}

2 : Data array in PHP

Before displaying records in a table we have created an array in PHP ( $php_data_array). While displaying the records in a table we store each record inside the PHP array.

$php_data_array[] = $row; // Adding to array

After displaying all the records in a table we have the $php_data_array with all the data collected from MySQL table. We can display the Json string like this.

echo json_encode($php_data_array);

3 : Transferring data from PHP to JavaScript to create the chart

By using json_encode we will create an json string which can be used to create the JavaScript two dimensional array.

echo "<script>
        var my_2d = ".json_encode($php_data_array)."
</script>";

4 : Adding data to Chart

Our JavaScript array my_2d stores all the data required for creating the chart. We need to display them in the format required by our Chart library.

for(i = 0; i < my_2d.length; i++)
    data.addRow([parseInt(my_2d[i][0]), my_2d[i][1],my_2d[i][2],
	parseInt(my_2d[i][3]),my_2d[i][4]]);

We used parseInt() function to convert input string value to Integer.

5. HTML part

We can show the chart inside a <DIV> tag and place the same any where we want to display the chart.

<div id=table_div></div>

options to add paging

var options ={page:true,pageSize:5,
  showRowNumber: false, width: 620, height: '50%'	}

Check google table chart basic for more details on paging and options

Using CSV file as data source

Once the php aray $php_data_array is created the rest of the code remain same. Here we are reading the student.csv file to create the $php_data_array. Once the array is created same code can be used. Zip file below contains the student.csv file as data source and the index-csv.php file to display the chart.
We used fgetcsv() to read csv file

$f_pointer=fopen("student.csv","r"); // file pointer
$php_data_array = Array(); // create PHP array
while(! feof($f_pointer)){
$ar=fgetcsv($f_pointer);
if (strlen($ar[0])>0) {  // to remove last line 
//echo print_r($ar); // print the array
$php_data_array[] = $ar; // Adding to array
}
}
//print_r($php_data_array);

echo "<script>
var my_2d=".json_encode($php_data_array)."
</script>";

3D Pie Chart Understanding different types of Charts
Pie Chart using MySQL data
Chart Basics

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect: