How to draw line chart from Two variables in two different MySQL tables

Question

I am new in php and learing how to draw Line chart for two different Temperature values in two different tables in MySQL. I can fetch the data from one table and draw it in a chart but I couldn't figured it out how to fetch the two temprature values from two different tables.

First table1: id temp 1 20

Second Table2: id temp 1 25

data.php code:

<?php 
 $servername = "localhost";
 $database = "test";
 $username = "test";
 $password = "12345";

 $connect = mysqli_connect($servername, $username, $password, $database);
 if (!$connect) {
  die("Connection failed: " . mysqli_connect_error());
  }

$query = ("SELECT * FROM Table1 UNION SELECT * FROM Table2");
$result = mysqli_query($connect, $query);

$data = array(); 

if ($result->num_rows > 0) { 
 while ($row = $result->fetch_assoc()) { 

 array_push($data, [$row['time'], floatval($row['temp']), $row['time'], floatval($row['temp'])]); 
 }
 }
  echo json_encode($data);
  mysqli_close($connect);

?>

the JS code :

setInterval(function () {

  google.charts.load('current', {'packages':['corechart', 'line']});
  google.charts.setOnLoadCallback(drawLineColors);

  function drawLineColors() {

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

  data.addColumn('string', 'Date');
  data.addColumn('number', 'Temperature');
   
  data.addColumn('string', 'Date');
  data.addColumn('number', 'Temperature2');
   

var jsonData = $.ajax({
    url: "data.php",
    dataType: "json",
    async: false
    }).responseText;
    
var obj = JSON.parse(jsonData);

data.addRows(obj);

let rowIndex = data.getNumberOfRows() - 1; //gets the row index of last row
let lastTime = data.getValue(rowIndex, 0); //gets the first column
let lastTemp = data.getValue(rowIndex, 1); //gets second column

let rowIndex2 = data.getNumberOfRows() - 1; //gets the row index of last row
let lastTime2 = data.getValue(rowIndex, 0); //gets the first column
let lastTemp2 = data.getValue(rowIndex,1); //gets third column

console.log(` ${lastTemp}, ${lastTemp2}`);
 
    $(".TempStatus").html(lastTemp + " °C");
    $(".Temp2Status").html(lastTemp2 + " °C");

   var options = {
    hAxis: {
      title: 'Time'
    },
    vAxis: {
      title: 'Sensors Scale'
    },
    colors: ['#a52714', '#097138']
  };
  var chart = new  google.visualization.LineChart(document.getElementById('chart_div'));

  chart.draw(data, google.charts.Line.convertOptions(options));
   }
    }, 5000);

 

How can I fetch the two temperature values from two tables?

Answer 1

first, let's query both tables separately.
then combine the data for the response, Table1 & Table2...

<?php
  $servername = "localhost";
  $database = "test";
  $username = "test";
  $password = "12345";

  $connect = mysqli_connect($servername, $username, $password, $database);
  if (!$connect) {
    die("Connection failed: " . mysqli_connect_error());
  }

  $data = array(
    'Table1' => array(),
    'Table2' => array()
  );

  $query = ("SELECT * FROM Table1");
  $result = mysqli_query($connect, $query);
  if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
      array_push($data['Table1'], [$row['time'], floatval($row['temp'])]);
    }
  }

  $query = ("SELECT * FROM Table2");
  $result = mysqli_query($connect, $query);
  if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
      array_push($data['Table2'], [$row['time'], floatval($row['temp'])]);
    }
  }

  echo json_encode($data);
  mysqli_close($connect);
?>

---

when drawing the chart, we can only have one x-axis.
so we will need to join the two tables on the time column.

but first, google charts only needs to be loaded once.
not every time we draw the chart.
so we'll load google charts first, before anything else.

and we don't know exactly how long it will take to get the data and draw the chart.
instead of using setInterval, we'll use setTimeout after the first draw has finished.

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

  var options = {
    hAxis: {
      title: 'Time'
    },
    vAxis: {
      title: 'Sensors Scale'
    },
    colors: ['#a52714', '#097138']
  };

  var chart = new google.visualization.LineChart(document.getElementById('chart_div'));

  google.visualization.events.addListener(chart, 'ready', function () {
    setTimeout(drawLineColors, 5000);
  });

  drawLineColors();

  function drawLineColors() {
    var data1 = new google.visualization.DataTable();
    data1.addColumn('string', 'Date');
    data1.addColumn('number', 'Temperature');

    var data2 = new google.visualization.DataTable();
    data2.addColumn('string', 'Date');
    data2.addColumn('number', 'Temperature');

    $.ajax({
      url: 'data.php',
      dataType: 'json',
    }).done(function (jsonData) {
      data1.addRows(jsonData.Table1);
      data2.addRows(jsonData.Table2);

      var joinData = google.visualization.data.join(data1, data2, 'full', [[0, 0]], [1], [1]);

      var rowIndex = data1.getNumberOfRows() - 1;
      var lastTime = data1.getValue(rowIndex, 0);
      var lastTemp = data1.getValue(rowIndex, 1);
      var rowIndex2 = data2.getNumberOfRows() - 1;
      var lastTime2 = data2.getValue(rowIndex2, 0);
      var lastTemp2 = data2.getValue(rowIndex2, 1);
      $(".TempStatus").html(lastTemp + " °C");
      $(".Temp2Status").html(lastTemp2 + " °C");

      chart.draw(joinData, options);
    });
  }
});

other notes...

1. async: false on ajax has been deprecated, use the done callback instead.
2. don't need the 'line' package in google charts, it is for material line charts.
   you're using a classic line chart --> google.visualization.LineChart
   material --> google.charts.Line
   as well, don't need to convert the options for a classic chart,
   removed --> google.charts.Line.convertOptions