File Name | Details |
---|---|
config.php | MySQLi Database connection details are stored here. |
sql_dump.txt | SQL Dump to create chart_data_column table with sample data. |
readme.txt | Instructions on how to run the script |
index.php | The main file to display records and the Pie chart. |
File Name | Details |
---|---|
config-pdo.php | PDO Database connection details are stored here. |
index-pdo.php | Using PDO the main file to display Pie Chart. |
File Name | Details |
---|---|
index-csv.php | Reading CSV file by using fgetcsv() and display chart. |
chart_data_line.csv | Comma separated value ( csv ) file with data. |
Download Zip file at end of this tutorial.
if($stmt = $connection->query("SELECT month,sale,profit,expanses, exp_fixed,exp_variable FROM chart_data_line")){
echo "No of records : ".$stmt->num_rows."<br>";
$php_data_array = Array(); // create PHP array
echo "<table>
<tr> <th>Month</th><th>Sale</th><th>Profit</th><th>Exp Fxd</th><th>Exp Vrv</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>";
}else{
echo $connection->error;
}
Using PDO : PHP Data Object we can connect to MySQL and retrive the data to create the array $php_data_array.
require "config-pdo.php";// Database connection
$query="SELECT month,sale,profit, exp_fixed,exp_variable
FROM chart_data_line";
$step = $dbo->prepare($query);
if($step->execute()){
$php_data_array = $step->fetchAll(); //
echo "<script>
var my_2d = ".json_encode($php_data_array)."
</script>";
}
$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);
echo "<script>
var my_2d = ".json_encode($php_data_array)."
</script>";
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([my_2d[i][0], parseInt(my_2d[i][1]),parseInt(my_2d[i][2]),parseInt(my_2d[i][3]),parseInt(my_2d[i][4])]);
We used parseInt() function to convert input string value to Integer.
JavaScript part is here .
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">
// Load the Visualization API and the corechart package.
google.charts.load('current', {packages: ['corechart']});
google.charts.setOnLoadCallback(drawChart);
function drawChart() {
// Create the data table.
var data = new google.visualization.DataTable();
data.addColumn('string', 'Month');
data.addColumn('number', 'Sale');
data.addColumn('number', 'Profit');
data.addColumn('number', 'Expanses');
data.addColumn('number', 'Exp_fixed');
data.addColumn('number', 'Exp_var');
for(i = 0; i < my_2d.length; i++)
data.addRow([my_2d[i][0], parseInt(my_2d[i][1]),parseInt(my_2d[i][2]),parseInt(my_2d[i][3]),parseInt(my_2d[i][4]),parseInt(my_2d[i][5])]);
var options = {
title: 'plus2net.com Sale Profit',
curveType: 'function',
width: 600,
height: 500,
legend: { position: 'bottom' }
};
var chart = new google.visualization.LineChart(document.getElementById('curve_chart'));
chart.draw(data, options);
}
///////////////////////////////
</script>
<div id="curve_chart"></div>
The chart drawn above can be displayed with live internet connection as the library is to be loaded from gstatic.com
Data is collected from MySQL database table so data can be edited by using script and the same will be reflected by the Chart. We have not included this part in the present script and there are many tutorials available here one handling data using PHP and MySQL.
You can read tutorials on other types of charts displayed by using data from database.$php_data_array
is created the rest of the code remain same. Here we are reading the chart_data_line.csv file to create the $php_data_array
. Once the array is created same code can be used. Zip file below contains the chart_data_line.csv file as data source and the index-csv.php file to display the chart. $f_pointer=fopen("chart_data_line.csv","r"); // file pointer
$php_data_array = Array(); // create PHP array
while(! feof($f_pointer)){
$ar=fgetcsv($f_pointer);
//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>";
var options = {
title: 'plus2net.com Sale Profit',
curveType: 'function',
width: 800,
height: 500,
legend: { position: 'bottom' },
animation:{'startup':true,
duration: 5000,
easing: 'out',
},
};
14-03-2020 | |
Hey thanks for this code you know I've been searching for this since January for our project in class thanks again more power |