Line Chart using data from MySQL table by PHP

Sample Line Chart in html

Download Zip file at end of this tutorial.

1 : Collecting data from MySQL database

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;
}

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>";
}

2 : Data array in PHP

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

echo json_encode($php_data_array);

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

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

4 : Adding data to Chart

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])]);

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

5. HTML part

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

Using CSV file as data source

$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>";

Adding animation to chart

var options = {
 title: 'plus2net.com Sale Profit',
 curveType: 'function',
width: 800,
 height: 500,
	 legend: { position: 'bottom' },
	 animation:{'startup':true,
        duration: 5000,
        easing: 'out',
      },
 };