Web Based JS Charts again.

Constricting web based charts is quite a simple job. Using SQL statement to pull a summary of the data, structure it according to the requirements of the JS Chart and there is the charts. On the previous post, I have mentioned the use of Rgraph (http://jnhiew.blogspot.sg/2016/04/javascript-charts.html) to achieve the purpose. This post is to show how it could be done.

First lets start with constructing the data. The format of the data is [0,1,2,3,4,5,6].

The SQL to get the data will be

SELECT SUM([yourvaluecolumn]) AS mysum,[yourfield] FROM mytable WHERE [yourcondition] GROUP BY [yourfield] ORDER BY [yourfield]

The PHP code to extract sqlite data is as follows

$db= new SQLITEDatabase('sqlitedb');
$query = $db->query('[your query statement]');

The result will be an array something like below

123, 'abc'
456, 'def'
789, 'ghi'
1012, 'klm'

To extract the array, use the following
$csv = "[";
$csvkey="[";
while ($data = $query->fetchArray(SQLITE_NUM){
   $csv.=$data[0].",";
   $csvkey .= "'". $data[1]."',";
}
$csv = rtrim($csv,",")."]";
$csvkey = rtrim($csvkey,",")."]";

The RTRIM command is to remove the last comma.

In the javascript replace  the "data:" and the "labels;" line with

data:<?php echo $csv; >,
labels::<?php echo $csvkey; >,

The above is only for simple charts with one set of data. If the data is required for multiple bar or multiple line chart like showing the set of data in groups by month, then the format is different for "data". The "labels" will remain the same. The format of the data is like

[[1,2,3],[4,5,6],[7,8,9]]

Before you do anything, you must extract the labels from the database. The reason is that there will not be a data result if the field value does not exist for a particular month causing the number of columns become variable. Furthermore, it is impossible to predict the labels without first extracting it before hand. Therefore, the first thing to do is to extract a label list and an 0 initiated associative array that includes all the labels.

$csvkey="[";
$csvary=array();
$qlabel = $db->query('SELECT DISTINCT [yourfield] from mytable order by [yourfield]');
while ($data = $qlabel->fetchArray(SQLITE_NUM){
   $csvkey.="'".$qlabel[0]."',";
   $csvary[$data[0]]=>0;
}
$csvkey = rtrim($csvkey,",")."]";

The SQL will be

SELECT SUM([yourvaluecolumn]) AS mysum,[yourmonth],[yourfield] FROM mytable WHERE [yourcondition] GROUP BY [yourmonth],[yourfield] ORDER BY [yourmonth],[yourfield]

The format of the result will be

123,  'March', 'abc'

PHP code will be as follow

$dta=array();
while ($data = $query->fetch(SQLITE_NUM){
   if (!isset($dta[$data[$data[1]])){
      $dta[$data[1]=$csvary;
   }
   $dta[$data[1]][$data[2]]=$data[0];
}
$data2="[";
$key2="";
foreach ($dta as $key=>$value){
   $data2.="[";
   $key2.="'".$key."',";
   foreach($value as $k2=>$v2){
       $data2.=$v2.",";
   }
   $data2=rtrim($data2,",");
   $data2.="],";
}
$data2=rtrim($data2,",")."]";
$key2=rtrim($key2.",");

Replace the two JS as before.

data::<?php echo $data2; >,
labels::<?php echo $key2; >,

Add an extra option to the option in JS. It will be shown as legends.

key::<?php echo $csvkey; >,

Obviously, there are times when there is a need to do multiple stacked bar group by month. I will leave it to you to figure out. Hint:- Third level array.