Using arrays to feed Google datetime chart plot

user_5513902 0
Viewed 90

I have meteorological data that I am retrieving through MySQL and can see it through phpMyAdmin. I am trying to plot stuff but I get no data in the plot.

My code is

<?php
    // connect to database
    $conn = mysqli_connect('localhost', 'root', 'station', 'meteobridge');

    // check connection
    if(!$conn){
        echo 'Connection error: ' . mysqli_connect_error();
    }

    // write query for data
    $sql = 'SELECT ID,TempInCur,TempOutCur FROM mystation';
    $dateFormat = 'SELECT DateTime FROM mystation';

    // make query & get result
    $result = mysqli_query($conn, $sql);
    $dtresult = mysqli_query($conn, $dateFormat);

    // fetch the resulting rows as an array
    $dailyMeasurements = mysqli_fetch_all($result, MYSQLI_ASSOC);   
    $dtArray = mysqli_fetch_all($dtresult, MYSQLI_ASSOC);
?>

 <!DOCTYPE html>
 <html>
    <?php include('templates/header.php'); ?>
        <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
            <script type="text/javascript">
              google.charts.load('current', {'packages':['corechart']});
              google.charts.setOnLoadCallback(drawChart);

              function drawChart() {
                var data = new google.visualization.DataTable();
                data.addColumn('datetime', 'Date');
                data.addColumn('number', 'TempIn');
                data.addColumn('number', 'TempOut');
                data.addRows([
                    [ <?php $dtArray[0]['DateTime'] ?>,  <?php echo $dailyMeasurements[0]['TempInCur'] ?>,<?php echo $dailyMeasurements[0]['TempOutCur'] ?>],
                    [ <?php $dtArray[1]['DateTime'] ?>,<?php echo $dailyMeasurements[1]['TempInCur'] ?>,<?php echo $dailyMeasurements[1]['TempOutCur'] ?>],
                    [ <?php $dtArray[2]['DateTime'] ?>,<?php echo $dailyMeasurements[2]['TempInCur'] ?>,<?php echo $dailyMeasurements[2]['TempOutCur'] ?>]
                ]);

                var options = {
                  title: 'Temperaturas',
                  //curveType: 'function',
                  legend: { position: 'bottom' }
                };

                var chart = new google.visualization.LineChart(document.getElementById('curve_chart'));
                chart.draw(data, options);
              }
            </script>
          <body>
            <div id="curve_chart" style="width: 900px; height: 500px"></div>
          </body>
    <?php include('templates/footer.php'); ?>
 </html>

The values for $dailyMeasurements[*]['TempInCur'] are 27.1, and 28.7 for $dailyMeasurements[*]['TempOutCur']. And the values of $dtArray[0]['DateTime'] are 2020-12-27 16:58:26, 2020-12-27 17:03:28, 2020-12-27 17:08:31.

The reason I am adding data with only the first few indexes of my arrays is because when I tried using the whole array (a long time ago), I would have more errors without understanding the problems. I tried making a simple example where I could try to figure out as a beginner what's going on and what is wrong. That made it possible to even get the following image of the resulting dataless plot.

enter image description here

If you have a way to feed the arrays directly to Google charts then that's even better! My intention was to figure out that after I am able to produce a plot.

mysqli google-visualization charts arrays php
2 Answers

I'm not sure but you get an associative array. You should convert this array to an object

const objDatas = JSON.parse($dtArray);

then

GoogleCharts.load("current", { packages: ['corechart'], callback: drawChart });

function drawChart() {

//use objDatas.yourProperty

It worked for me

user_132515580

first, the sql. it looks like all columns are coming from the same table,
so why not include them all in the same query...?

from...

$sql = 'SELECT ID,TempInCur,TempOutCur FROM mystation';
$dateFormat = 'SELECT DateTime FROM mystation';

to...

$sql = 'SELECT DateTime,TempInCur,TempOutCur FROM mystation';

next, build your array in php...

  // make query & get result
  $result = mysqli_query($conn, $sql);

  $rows = array();
  while($row = mysqli_fetch_array($result)){
    $rows[] = array($row['DateTime'], $row['TempInCur'], $row['TempOutCur']);
  }

then write it to the page in the addRows method...

data.addRows(<?php echo json_encode($rows); ?>);

see following snippet...

<?php
  // connect to database
  $conn = mysqli_connect('localhost', 'root', 'station', 'meteobridge');

  // check connection
  if(!$conn){
      echo 'Connection error: ' . mysqli_connect_error();
  }

  // write query for data
  $sql = 'SELECT DateTime,TempInCur,TempOutCur FROM mystation';

  // make query & get result
  $result = mysqli_query($conn, $sql);

  $rows = array();
  while($row = mysqli_fetch_array($result)){
    $rows[] = array($row['DateTime'], $row['TempInCur'], $row['TempOutCur']);
  }
?>

<!DOCTYPE html>
<html>
  <?php include('templates/header.php'); ?>
  <head>
    <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
    <script type="text/javascript">
      google.charts.load('current', {'packages':['corechart']});
      google.charts.setOnLoadCallback(drawChart);

      function drawChart() {
        var data = new google.visualization.DataTable();
        data.addColumn('datetime', 'Date');
        data.addColumn('number', 'TempIn');
        data.addColumn('number', 'TempOut');
        data.addRows(<?php echo json_encode($rows); ?>);

        var options = {
          title: 'Temperaturas',
          legend: { position: 'bottom' }
        };

        var chart = new google.visualization.LineChart(document.getElementById('curve_chart'));
        chart.draw(data, options);
      }
    </script>
  </head>
  <body>
    <div id="curve_chart" style="width: 900px; height: 500px"></div>
  </body>
  <?php include('templates/footer.php'); ?>
</html>
user_50907710
Related