Search code examples
mysqlajaxtimestampgoogle-visualizationgoogle-gauges

How to add latest TimesStamp to Google Gauge

I need help adding latest TimesStamp to page that displays Google gauge. I have made gauge work and auto refresh without the need to refresh the page, but now I need to display on it, or next to it when the latest entry in database was made (displaying TimesStamp of value that is currently displayed in gauge). Here's my code so far:

Chart.html

<!DOCTYPE html>
<html lang="en">
  <head>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.0/jquery.min.js"></script>
    <script src="https://www.gstatic.com/charts/loader.js"></script>


    <script>
      google.charts.load('current', {
        packages: ['gauge']
      }).then(function () {
        var options = {
          width: 800, height: 240,
          greenFrom: 98, greenTo: 100,
          yellowFrom:90, yellowTo: 98,
          minorTicks: 5
        };

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

        drawChart();

        function drawChart() {
          $.ajax({
            url: 'getdata.php',
            dataType: 'json'
          }).done(function (jsonData) {
            // use response from php for data table
            var data = google.visualization.arrayToDataTable(jsonData);
            chart.draw(data, options);

            // draw again in 5 seconds
            window.setTimeout(drawChart, 5000);
          });
        }
      });
    </script>

  </head>
  <body>


    <div id="chart_div" style="width: 800px; height: 240px;"></div>
  </body>
</html>

And here's getdata.php

<?php
  $servername = "localhost";
  $username = "u644759843_miki";
  $password = "plantaze2020!";
  $dbname = "u644759843_plantazeDB";

  // Create connection
  mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
  $conn = mysqli_connect($servername, $username, $password, $dbname);
  $conn->set_charset('utf8mb4');

  $sql = "SELECT ProductPurity FROM `Precizno ProductPurity` ORDER BY TimesStamp DESC LIMIT 1";
  $result = mysqli_query($conn, $sql);

  // create data array
  $data = [];
  $data[] = ["Label", "Value"];

  // output data of each row
  while($row = mysqli_fetch_assoc($result)) {
      $data[] = ["ProductPurity", (float) $row["ProductPurity"]];
  }

  mysqli_close($conn);

  // write data array to page
  echo json_encode($data);
?>
Solution

  • we need to include the timestamp in the data we return from php.
    first, add the field to the select statement, here... 

    $sql = "SELECT ProductPurity, TimesStamp FROM `Precizno ProductPurity` ORDER BY TimesStamp DESC LIMIT 1";

    next, we use a variable to save the timestamp... 

    // create data array
$data = [];
$data[] = ["Label", "Value"];
$stamp = null;

    then, in the while loop, we save the value of the timestamp... 

    // output data of each row
while($row = mysqli_fetch_assoc($result)) {
    $data[] = ["ProductPurity", (float) $row["ProductPurity"]];
    $stamp = $row["TimesStamp"]
}

    finally, we combine both the chart data and timestamp in an object to send to the page. 

    $data = array('rows' => $data, 'timestamp' => $stamp);

    following is the updated php snippet... 

    <?php
  $servername = "localhost";
  $username = "u644759843_miki";
  $password = "plantaze2020!";
  $dbname = "u644759843_plantazeDB";

  // Create connection
  mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
  $conn = mysqli_connect($servername, $username, $password, $dbname);
  $conn->set_charset('utf8mb4');

  $sql = "SELECT ProductPurity, TimesStamp FROM `Precizno ProductPurity` ORDER BY TimesStamp DESC LIMIT 1";
  $result = mysqli_query($conn, $sql);

  // create data array
  $data = [];
  $data[] = ["Label", "Value"];
  $stamp = null;

  // output data of each row
  while($row = mysqli_fetch_assoc($result)) {
      $data[] = ["ProductPurity", (float) $row["ProductPurity"]];
      $stamp = $row["TimesStamp"]
  }

  mysqli_close($conn);

  // write data array to page
  $data = array('rows' => $data, 'timestamp' => $stamp);
  echo json_encode($data);
?>

    then on the html page, we need to adjust how we receive the data...

    to receive the chart data, we need to use the 'rows' property from the data. 

    // use response from php for data table
var data = google.visualization.arrayToDataTable(jsonData.rows);  // <-- add .rows

    and we can receive the timestamp as follows... 

    jsonData.timestamp

    not sure how you want to display the timestamp, here a <div> is used.
    so to update the new <div> element... 

    document.getElementById('timestamp').innerHTML = jsonData.timestamp;

    following the updated html snippet... 

    <!DOCTYPE html>
<html lang="en">
  <head>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.0/jquery.min.js"></script>
    <script src="https://www.gstatic.com/charts/loader.js"></script>
    <script>
      google.charts.load('current', {
        packages: ['gauge']
      }).then(function () {
        var options = {
          width: 400, height: 120,
          redFrom: 90, redTo: 100,
          yellowFrom:75, yellowTo: 90,
          minorTicks: 5
        };

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

        drawChart();

        function drawChart() {
          $.ajax({
            url: 'getdata.php',
            dataType: 'json'
          }).done(function (jsonData) {
            // use response from php for data table
            var data = google.visualization.arrayToDataTable(jsonData.rows);
            chart.draw(data, options);

            // update timestamp
            document.getElementById('timestamp').innerHTML = jsonData.timestamp;

            // draw again in 5 seconds
            window.setTimeout(drawChart, 5000);
          });
        }
      });
    </script>
  </head>
  <body>
    <div id="timestamp"></div>
    <div id="chart_div" style="width: 400px; height: 120px;"></div>
  </body>
</html>