Need to select first row of day in MySQL

[axslinger]

I know this is a PHP forum, but since I'm calling a php 'include', I thought I'd try here.

 

I have a Raspberry Pi that is grabbing reading from sensors every 10 minutes and putting them into a MySQL database. The table is called, WEATHER_MEASUREMENT.

 

In order to show day-to-day trendsin barometric pressure for 7 days, I would like to grab the first (or last) reading of each day and I only need the TIMESTAMP field (With just the date, not the time) which I call, 'CREATED' and the 'AIR_PRESSURE' field. Then I want to display them in a 2 column table format with the date and the reading. I have this in my index.php file:

 

//Query for 7 days of barometric pressure
$result = mysqli_query($link, 'SELECT AIR_PRESSURE, CREATED FROM WEATHER_MEASUREMENT ORDER BY ID DESC LIMIT 7');
if (!$result)
{  
 $error = 'Error fetching 7 day: ' . mysqli_error($link);  
 include 'error.html.php';  
 exit();  
}

while ($row = mysqli_fetch_array($result))  
{  
  $sevdaypress[] = $row['AIR_PRESSURE'];
  $mydate[] = $row['CREATED'];
  }

 

Then in my readings.html.php file I have this:

		<?php foreach ($sevdaypress as $spress): ?>
			<?php foreach ($mydate as $thedate): ?>
			<?php echo htmlspecialchars($thedate.': '.$spress, ENT_QUOTES, 'UTF-8')?><br>
			<?php endforeach; ?>
		<?php endforeach; ?>		

My index.php file has the line:

include 'readings.html.php';

I know this isn't anywhere close and I'm missing some things, like grouping and somehow only selecting one row per day. I've done tons of searching but due to being really new at this, I'm coming up empty. As I said, everything else is working fine. I'm sure this is a rudimentary issue and I apologise ahead of time for such a noob question.

 

I've include the complete files for reference. Everything else I'm doing is working as expected. Thanks again.

 

index.php

readings.html.php

Edited April 27, 2017 by axslinger

[NotionCommotion]

First of all, for these lines...

        <?php foreach ($sevdaypress as $spress): ?>
            <?php foreach ($mydate as $thedate): ?>
            <?php echo htmlspecialchars($thedate.': '.$spress, ENT_QUOTES, 'UTF-8')?><br>
            <?php endforeach; ?>
        <?php endforeach; ?>

... you don't need all the php tags, and while maybe valid (I don't know if it is), I think there are cleaner ways to show your script such as:

foreach ($sevdaypress as $spress) {
    foreach ($mydate as $thedate) {
        echo htmlspecialchars($thedate.': '.$spress, ENT_QUOTES, 'UTF-8').'<br>';
    }
}

Secondly, your query provided a bunch of records with pressure and dates.  I don't think your use of nested loops above is really what you want.