Store database infos into json array to use in chart.js

Question

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"}]

Remove `LIMIT 1` from your query of you want all rows. – GMB Nov 08 '19 at 22:58 — GMB, Nov 08 '19 at 22:58
I got it, had to change the SQL part – Joao Nov 08 '19 at 23:02 — Joao, Nov 08 '19 at 23:02

score 0 · Answer 1 · answered Nov 08 '19 at 23:03

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

answered Nov 08 '19 at 23:03

GMB

216,147
25
84
135

How would I be able to only store sensor1 infor from this code that you posted? – Joao Nov 08 '19 at 23:07
And how you output that? – Joao Nov 08 '19 at 23:07
@Joao: do you want only column `sensor1` in the resultset, not other columns? – GMB Nov 08 '19 at 23:09
If you could get other columns too it would be great, I would need an array for each column – Joao Nov 08 '19 at 23:10
@Joao: your expected result is unclear to me. Would you please edit your question to show the result that are you are looking to generate? – GMB Nov 08 '19 at 23:25

score -1 · Answer 2 · answered Nov 08 '19 at 23:03

<?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);
?>

Store database infos into json array to use in chart.js

2 Answers2