Display most recent MySQL entry to web page

MySQL database
#1

First, don’t think for a minute I haven’t researched this until my head is ready to explode. :-\

First a little background…

I’m using a Raspberry Pi to collect humidity, temperature and barometric pressure. I’m successfully adding it to a MySQL database called ‘weather’. I have a table called ‘WEATHER_MEASUREMENT’ with columns called, ‘id’, ‘AMBIENT_TEMPERATURE’, ‘AIR_PRESSURE’, ‘AIR_QUALITY’, ‘HUMIDITY’ and ‘CREATED’, which is a date/time field.

I have Lighttpd installed and it is working; I can display various php pages without issue, such as the phpinfo() page. I can log into my database and display single fields and I can get other examples to display data from my DB.

I read then sensors and add a new row to my database every 10 minutes. There is one caveat; I don’t have an air quality sensor currently so that column’s readings are set to NULL.

What I want to do seems simple enough but I can’t find an example anywhere on the net, and I have been searching for two weeks. I want to display the most current row, minus the ‘id’ field. I would like the presentation to be something like:

Current temperature: <AMBIENT_TEMPERATURE>
Current Humidity:
Current Bar. Pressure: <AIR_PRESSURE>

My thinking is I need to do a SELECT that grabs the fields I want, assign them to an array then pass them to my index.php

I’m currently grabbing all ‘AMBIENT_TEMPERATURE’ readings and displaying them in a continuous row down the side of the browser. The code I’m using is as such…:

[php]
// Query all temperatures
$result = mysqli_query($link, ‘SELECT AMBIENT_TEMPERATURE FROM WEATHER_MEASUREMENT’);
if (!$result)
{
$error = 'Error fetching temperatures: ’ . mysqli_error($link);
include ‘error.html.php’;
exit();
}

while ($row = mysqli_fetch_array($result))
{
$temps[] = $row[‘AMBIENT_TEMPERATURE’];
}
include ‘temps.html.php’;
[/php]

And ‘temps.html.php’ looks like this:

[php]

List of Temps

Here are all the readings in the database:

<?php foreach ($temps as $temp): ?>

<?php echo htmlspecialchars($temp, ENT_QUOTES, 'UTF-8'); ?>

<?php endforeach; ?> [/php]

As I’ve said, I searched high and low for displaying the most recent entry and am coming up empty. I used this code just to get to the point where I could actually get data from my DB into a web page, and that was a small victory. I’ve tried to adapt this code to select and display multiple columns but and coming up dry. Any help is appreciated.

#2

All you need to do is use ORDER BY and set the direction you want the results with ASC or DESC. Your data is already in an array when it comes out of the database. You do not need to put it in yet another one.

#3

I guess I wasn’t clear enough…

I understand that changing the order would put the most recent temperature at the top. That’s not the issue.

  1. I don’t want all readings. I only want the current reading.
  2. That still doesn’t address that I also want the barometric pressure and humidity across the screen, as I said in my original post.
#4
  1. There are two ways you could do it. SELECT MAX time_column or do the ORDER BY LIMIT 1.
#5

The goal for any sql query is to get the data that you want in the order that you want it. If you only want a limited number of rows, such as one, you would add a LIMIT clause to the query.

Perhaps some mysql 101 would be in ORDER (sql pun intended.) The following is the SELECT query syntax prototype/definition, with commonly used elements shown in red -

SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] select_expr [, select_expr ...] [FROM table_references [WHERE where_condition] [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]] [HAVING where_condition] [ORDER BY {col_name | expr | position} [ASC | DESC], ...] [LIMIT {[offset,] row_count | row_count OFFSET offset}] [PROCEDURE procedure_name(argument_list)] [INTO OUTFILE 'file_name' [CHARACTER SET charset_name] export_options | INTO DUMPFILE 'file_name' | INTO var_name [, var_name]] [FOR UPDATE | LOCK IN SHARE MODE]]

Given that your id column is/should be an auto-increment primary index, you would try to use it whenever possible. Using the data/time to find data would require that you make it an index as well so that the queries would operate as efficient as possible.

So, what are you trying to get your query to do, using one possible method -

SELECT a list of the column names you want. If you always list out the columns, rather than use *, your code will be self-documenting, most efficient (won’t be selecting and transferring data you don’t need), and have less implementation problems and errors in using the data from the query.

FROM your_table.

No WHERE term. You want all the data in the table to be considered. A WHERE clause would be used if you want a range or a specific value to be matched.

No GROUP BY term. This is used with aggregate functions that operate on the grouped data.

ORDER BY id DESC. Order the data by the id field in descending order.

LIMIT 1. Get only the first row in the result set, which happens to be the row with the highest id value.

This query will return at most one row (if the table is empty, there would be no row.) Your current php code would fetch the data into an array variable, edit: if it was general purpose and storing the entire row, rather than a single column from the row. end of edit However, if you are expecting a query to return a maximum of one row, just fetch the data without using a loop.

#6

Well, I’ve beat my head against the wall trying to decipher the syntax you pasted. I guess I’ll keep beating.

#7

Hello,

To me it looks quite clear what phdr is trying to tell you, hope i am not wrong:

SELECT AMBIENT_TEMPERATURE,HUMIDITY,AIR_PRESSURE
FROM WEATHER_MEASUREMENT
ORDER BY id DESC
LIMIT 1

Therefore you should end up with something like this (i guess):

‘SELECT ambient_temperature,humidity,air_pressure FROM weather_measurement ORDER BY id DESC LIMIT 1’

Sponsor our Newsletter | Privacy Policy | Terms of Service