Multi line Google Line Chart, using data from mySQL db

Question

I'm going through a bit of a learning process, in order to create a small database backed reporting system for my company.

The intent is to draw a multi line chart using Google Charts, based on a mysql database.

I've managed to get the data to echo from the mysql database, but it's not generating the chart. All I get is the echo, and a blank space where the chart should be. The echo is shown for debugging purposes.

Here is the code :

query($qry);

if($result === FALSE) {
    echo mysql_errno($result) .": ". mysql_error($result) ."/n";
    die(mysql_error());
}

$rows = array();
$table = array();
$table['cols'] = array(
                        array('label' => 'Time', 'type' => 'datetime'),
                        array('label' => 'Probe 1', 'type' => 'number'),
                        array('label' => 'Probe 2', 'type' => 'number'),
                        array('label' => 'Probe 3', 'type' => 'number'),
                        array('label' => 'Probe 4', 'type' => 'number')
                        );

while($r = mysqli_fetch_assoc($result)) {

    $temp = array();
    $temp[] = array($r['time']);

    $temp[] = array($r['p1']);
    $temp[] = array($r['p2']);              
    $temp[] = array($r['p3']);
    $temp[] = array($r['p4']);

    $rows[] = array('c' => $temp);  
}

$table['rows'] = $rows;

$jsonTable = json_encode($table);
echo $jsonTable;
?>

And this is the 'echo' output

{"cols":[{"label":"Time","type":"datetime"},{"label":"Probe 1","type":"number"},{"label":"Probe 2","type":"number"},{"label":"Probe 3","type":"number"},{"label":"Probe 4","type":"number"}],"rows":[{"c":[["03:02:07"],["270.26"],["298.40"],["111.54"],["228.06"]]},{"c":[["03:28:42"],["273.23"],["190.43"],["245.69"],["283.21"]]},{"c":[["07:26:04"],["144.33"],["217.26"],["206.53"],["167.68"]]},{"c":[["12:13:20"],["153.15"],["277.23"],["167.20"],["240.88"]]}]}

This is test data, using a test query on the db. Once I understand the formatting to render the chart, it will be setup to allow the user to select which date to view, etc.

This was the closest existing question I can find, but doesn't seem to answer the question.

Not able to generate a Google Chart using MySQL table data as the data source

Following the answer of @MickMackusa, I managed to hack this together to get it to work, by ensuring the mysql/php array was output in a manner acceptable to Google Charts.

Thanks to @MickMacUSA for his assistance.

The final, working code, is below.

query($qry);

if($result === FALSE) {
    echo mysqli_errno($result) .": ". mysqli_error($result) ."/n";
    die(mysqli_error());
}
    $i = 0; //iteration counter - start at 0

    $totalRows = mysqli_num_rows($result); // we need this to know when to change the output
    $targetRows = $totalRows - 1; //row indies start from 0, not 1.

    foreach ($result as $row){ 

        $comTime = str_replace(":",",",$row['time']); // for each row, remove the : and put , in its place
        if ($targetRows == $i) { // if the index is the same value as the target (ie, it's the last row)...

            $temp = "[[".$comTime."],".($row['p1']).",".($row['p2']).",".($row['p3']).",".($row['p4'])."]". PHP_EOL;
            } else {
            $temp = "[[".$comTime."],".($row['p1']).",".($row['p2']).",".($row['p3']).",".($row['p4'])."],". PHP_EOL;
            }
        $i = $i + 1; 
        $rows[] = $temp; 
    }

 $table = $rows;
 $data = implode($table); //format the table as a single string, with line returns

//echo $i;
//echo $data;

?>

mickmackusa · Accepted Answer

Your number values must be formatted differently and you want timeofday not datetime.

According to: https://developers.google.com/chart/interactive/docs/reference#dataparam

Format your data to look like this:

{cols:
    [
        {"label":"Time","type":"timeofday"},
        {"label":"Probe 1","type":"number"},
        {"label":"Probe 2","type":"number"},
        {"label":"Probe 3","type":"number"},
        {"label":"Probe 4","type":"number"}
    ],
rows:
    [
        {c:[{v:[03,02,07],f:'03:02:07'},{v:270.26},{v:298.40},{v:111.54},{v:228.06}]},
        {c:[{v:[03,28,42],f:'03:28:42'},{v:273.23},{v:190.43},{v:245.69},{v:283.21}]},
        {c:[{v:[07,26,04],f:'07:26:04'},{v:144.33},{v:217.26},{v:206.53},{v:167.68}]},
        {c:[{v:[12,13,20],f:'12:13:20'},{v:153.15},{v:277.23},{v:167.20},{v:240.88}]}
    ]
}

And you must echo it in the javascript:

change:

to:

And put your javascript code block just before your tag.

This is the full working code using the above data format that I tested on my server:

This is an alternative format that will be simpler/clearer/easier to build/comprehend using your mysqli results:

See the SO Demo provided by WhiteHat:

google.charts.load('current', {
  callback: drawChart,
  packages: ['corechart', 'table']
});

function drawChart() {
  var data = new google.visualization.DataTable({cols: [
      {"label":"Time","type":"timeofday"},
      {"label":"Probe 1","type":"number"},
      {"label":"Probe 2","type":"number"},
      {"label":"Probe 3","type":"number"},
      {"label":"Probe 4","type":"number"}
    ],
    rows: [
      {c:[{v:[03,02,07],f:'03:02:07'},{v:270.26},{v:298.40},{v:111.54},{v:228.06}]},
      {c:[{v:[03,28,42],f:'03:28:42'},{v:273.23},{v:190.43},{v:245.69},{v:283.21}]},
      {c:[{v:[07,26,04],f:'07:26:04'},{v:144.33},{v:217.26},{v:206.53},{v:167.68}]},
      {c:[{v:[12,13,20],f:'12:13:20'},{v:153.15},{v:277.23},{v:167.20},{v:240.88}]}
    ]
  });

  var table = new google.visualization.Table(document.getElementById('chart_0'));
  table.draw(data);

  var options = {
      title: 'Recorded Temperatures',
      legend: {position: 'bottom' },
      width: 800,
      height: 600,
      hAxis: {
        format: 'hh:mm:ss'
      }
  };
  var chart = new google.visualization.LineChart(document.getElementById('chart_1'));
  chart.draw(data, options);
}

Multi line Google Line Chart, using data from mySQL db

Answers (1)

This is an alternative format that will be simpler/clearer/easier to build/comprehend using your mysqli results:

Related Questions