Line Chart From Json Response In Html / Javascript / Php

I'm trying to create a single line chart of a json response(sql) in html/javascript. What im trying to create is a chart with only one line of the tag_name, in the case below: 'pho

Solution 1:

first, don't recommend mixing php and javascript in the same file

separate the html / javascript from the php

use ajax to get the data from php to javascript

for google charts, use the json format found here...
Format of the Constructor's JavaScript Literal data Parameter

this will allow you to create the google data table, directly from the json

getdata.php

require_once 'db_connection.php';
header('Content-type: application/json');

$data = array();
$Chart = "SELECT *
             FROM (
                  SELECT *,
                  @rn := if( @tag_name = tag_name,
                   @rn + 1,
                   if(@tag_name := tag_name, 1, 1)
                ) as tag_count
            FROM waardes
            CROSS JOIN ( SELECT @rn := 0, @tag_name := '') as vars
            ORDER BY tag_name
        ) as T
      WHERE tag_count < 11 AND machine_id LIKE 3 AND tag_name LIKE 'phone 4'
      ORDER BY datetime DESC";
$result = mysqli_query($connection, $Chart);


$data = array();
$data['cols'] = array(
    array('label' => 'Date', 'type' => 'string'),
    array('label' => 'phone 4', 'type' => 'number')
);
$data['rows'] = array();

while ($row = mysqli_fetch_array($result)) {
  if ($row["int_value"] == 0 && $row["real_value"] == 0.0 && $row["bool_value"] != "") {

      $row = array();
      $row[] = array('v' => (string) $row['datetime']);
      $row[] = array('v' => (float) $row['bool_value']);
      $data['rows'][] = array('c' => $row);

  } elseif ($row["int_value"] == 0 && $row["real_value"] != 0 && $row["bool_value"] == "") {

      $row = array();
      $row[] = array('v' => (string) $row['datetime']);
      $row[] = array('v' => (float) $row['real_value']);
      $data['rows'][] = array('c' => $row);

  } elseif ($row["int_value"] != 0 && $row["real_value"] == 0 && $row["bool_value"] == "") {

      $row = array();
      $row[] = array('v' => (string) $row['datetime']);
      $row[] = array('v' => (float) $row['int_value']);
      $data['rows'][] = array('c' => $row);

  }
}

$data['rows'] = $rows;

echo json_encode($data);

---

JavaScript

google.charts.load('current', {
    packages: ['line']
}).then(function () {

    $.ajax({
        url: 'getdata.php',
        dataType: 'json'
    }).done(function (jsonData) {

        var data = new google.visualization.DataTable(jsonData);

        var options = {
            chart: {
                title: 'Values'
            },
            width: 900,
            height: 500
        };

        var chart = new google.charts.Line(document.getElementById('chart_div'));
        chart.draw(data, google.charts.Line.convertOptions(options));

    }).fail(function (jq, text, errMsg) {
      console.log(text + ': ' + errMsg);
    });

});

---

this will give you a better structure to handle multiple charts / data tables

now, if you wanted to use a real date in the chart, instead of a string (as in the above php)
it would allow you to use chart options such as format, for the x-axis

you can pass the date value as a string, in the following format found here...
Dates and Times Using the Date String Representation

"Date(Year, Month, Day, Hours, Minutes, Seconds, Milliseconds)"

to do this, change the column type to date...

    array('label' => 'Date', 'type' => 'date'),

then when loading the data, using the following to format as above...

      $rowDate = "Date(".date_format($row['datetime'], 'Y').", ".((int) date_format($row['datetime'], 'm') - 1).", ".date_format($row['datetime'], 'd').", ".date_format($row['datetime'], 'H').", ".date_format($row['datetime'], 'i').", ".date_format($row['datetime'], 's').")";

      $row = array();
      $row[] = array('v' => (string) $rowDate);
      $row[] = array('v' => (float) $row['bool_value']);
      $data['rows'][] = array('c' => $row);

note: month numbers are zero-based in javascript...