Jump to content

Need to select first row of day in MySQL


axslinger

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.