Trouble following a tutorial

[Sandysound]

I am following a tutorial to create a chart from my database.
https://www.element14.com/community/community/stem-academy/blog/2018/03/19/sensor-dashboard-for-raspberry-pi-using-mysql-php-and-highcharts
To get the data from the database, it provides the following code:

?php

$username=“username”;
$password=“password”;
$database=“database”;

$conn = mysqli_connect(“localhost”, $username, $password, $database);

if (mysqli_connect_error()) {

echo "Failed to connect to database: " . mysqli_connect_error();
exit();

}

$queryT1 = ‘SHOW Stamp, Temp1 FROM table’;
$resultT1 = mysqli_query($conn, $queryT1);

$dateTemp1 = array();
$index1 = 0;

while ($row1 = mysqli_fetch_array($resultT1, MYSQLI_NUM))

{
$dateTemp1[$index1]=$row1;
$index1++;
}

echo json_encode($dateTemp1, JSON_NUMERIC_CHECK);

mysql_close();

?>

This only returns

[]

without any of the data that I was expecting to see.
The connection to the database is good, and the table is populated, so I do not know why the returning page is empty?!
This data would then be used for a Highchart, which I believe is why it tries to prepare the data as an array, but I do not follow what is happening. Is there a better way?
The data gets called into the chart using html:
data: <?php echo json_encode($dateTemp1, JSON_NUMERIC_CHECK);?>
Cheers,
Sandy

[Sandysound]

The bit where I get really stuck is the line:
$dateTemp1[$index1]=$row1;

which in my head translates to:
array()[0]=mysqli_fetch_array((mysqli_query(mysqli_connect(“localhost”, “username”, “password”, “database”), SHOW Stamp, Temp1 FROM sensors), MYSQLI_NUM);

And therefor I am not getting data into the array?

[skawid]

@Sandysound

SHOW Stamp, Temp1 FROM table

This isn’t a valid MySQL query: the first word should be SELECT. That’s the most obvious problem here.

This will mean your $resultT1 is not a result set, so attempting to loop over it should cause an error. You should be seeing errors here if your error reporting is configured correctly, but many environments don’t have it set up properly by default. Where are you running this code?

[Sandysound]

Ahh ok, I have found several issues with this tutorial, its from 2018 so I thought it might still be relevant. Anyway, it’s got me digging deep and forcing me to learn alot so perhaps it’s still doing its job!

Thanks, I will make an adjustment and try again.

I am running it on my Pi, on an Apache server (I think) as the default index.php.
The goal is to see the data from the sensors I have hooked up to the pi in a graph.

[whispers]

If the data in the table already… then the Pi and sensors are irrelevant at this point.

Its all about getting the data from the table now and using to output/display things in a front end graph/chart layout.

I mess around with this stuff a lot, although to be fair… I am usually using Arduino’s…etc for my sensors, and not the direct GPIO pins on the RPi itself… but I always do a full LAMP install on my RPi’s… so I can run/host PHP based websites/interfaces…

[Sandysound]

Yeah, a LAMP, I think thats basically what I am doing, just following a tutorial that doesn’t involve sending data to a site that charges money to display the data. I know the paid options look pretty but its also a useful learning process for me at the same time.

I now have the data in the graph! Thanks for spotting the issue in the query. At least now I can see the data!

But now I have a new issue ha ha. Surprise surprise.

The datetime stamp I am using is different to the tutorial - something about counting the seconds from UTC or something. I have saved it in YY-MM-DD HH:MM:SS format instead, because that just helps my brain when I look at the data in the table. I can change the format but I prefer to keep it this way if I can.
What I don’t understand is how the javascripts are interpreting this data, as it is showing the data as being from the first of jan, and not today’s date…
Since this is a new issue perhaps its time for another post.
Cheers!

[skawid]

Your code expects to see that date / time as a number of seconds since a certain date - this is known as a unix time stamp and is a useful standard way to store a time. Because you’ve changed the format in your data store, it’s now seeing a date string which it’s not written to deal with. Change the column type back and you’ll be fine. Depending on the software you’re using to look at the table, you may find it has configuration to view a unix time stamp as a date without actually storing it differently.

It helps my brain

You’re a dev now, get used to your brain being worked hard.