LaVOZs

The World’s Largest Online Community for Developers

'; php - Store database infos into json array to use in chart.js - LavOzs.Com

I'm trying to store MySQL info into a JSON array so I can use it in Chart.js to refresh the chart without refreshing the page. I tried adding sensors info to an array but for some reason, it is only outputting the last info sent. My database has 3 columns with the name as sensor1, sensor2, sensor3. This is what I tried:

<?php 

  session_start();

  if(!isset($_SESSION['usersId']))
  {
    header("Location: ../index.php");
    exit();
  }
  else
  {
    include_once 'includes/dbh.inc.php';
  }

  $id = $_SESSION['userId']; 
  $dBname = "infosensor";
  $conn = mysqli_connect($servername, $dBUsername, $dBPassword, $dBname);

  $sql = "SELECT sensor1, sensor2, sensor3 FROM `$id` ORDER BY id DESC LIMIT 1;";

  $result = mysqli_query($conn, $sql);
  $row = mysqli_fetch_assoc($result);
  $jsonsensor = array();
  if ($row)
  {
    $jsonsensor[] = $row; 
  }

  echo json_encode($jsonsensor);
?>

The output:

[{"sensor1":"5","sensor2":"5","sensor3":"0"}]

This query:

SELECT sensor1, sensor2, sensor3 FROM `$id` ORDER BY id DESC LIMIT 1

Will always return at most 1 record, because of the LIMIT 1 clause. If you want all records, remove that clause from your query.

Also it should be noted that is is possible to generate JSON directly from MySQL, which should have better performance than using php in between. The following query gives you a unique record, with a unique column called js, that contains a JSON array that aggregates each table row as a JSON object:

select json_arrayagg(
    json_object(
        'sensor1', sensor1, 
        'sensor2, 'sensor2, 
        'sensor3', sensor3
    )
) js
from mytable
<?php 

  session_start();

  if(!isset($_SESSION['usersId']))
  {
    header("Location: ../index.php");
    exit();
  }
  else
  {
    include_once 'includes/dbh.inc.php';
  }

  $id = $_SESSION['userId']; 
  $dBname = "infosensor";
  $conn = mysqli_connect($servername, $dBUsername, $dBPassword, $dBname);

  $sql = "SELECT sensor1, sensor2, sensor3 FROM `$id`;";

  $result = mysqli_query($conn, $sql);
  $jsonsensor = array();
  if (mysqli_num_rows($result) > 0)
  {
    while ($row = mysqli_fetch_assoc($result))
    {
      $jsonsensor[] = $row;
    } 
  }

  echo json_encode($jsonsensor);
?>

Related
Create ArrayList from array
How do I check if an array includes a value in JavaScript?
Can comments be used in JSON?
How to append something to an array?
Deleting an element from an array in PHP
What is the correct JSON content type?
Why does Google prepend while(1); to their JSON responses?
Loop through an array in JavaScript
How do I remove a particular element from an array in JavaScript?
For-each over an array in JavaScript?