troubleshooting Question

Filter MySQL results by max value for each 24 hour cycle using PHP

Avatar of williamskellorn
williamskellorn asked on
PHPMySQL ServerSQL
15 Comments1 Solution1510 ViewsLast Modified:
I previous asked a question (https://www.experts-exchange.com/Programming/Languages/Scripting/PHP/Q_28153645.html) the solution for which I need to further refine - only after closing the question did I realise the solution didn't quite get what I required.

Using the simplified php below (and attached) on the attached sql example, For each PTT value, I need to return the values from the row with the latest timestamp that has the best location_class value - during each 24 hour cycle.

Currently the location_class value returned is the best for the time window, but the rest of the values returned are from the first row (with earliest timestamp) encountered with a qualifying value for location_class (1,2 or 3), instead of the row with latest timestamp that has the best location_class.

How can I amend the query to achieve this, preferably without requiring a further php loop to iterate through?

At this point I'd be happy for the row with the best location_class, irrespective of whether it is the latest row with that class.

Note - The sample SQL isn't designed to test the requirement for results for each 24 hour cycle - just the issue of selecting the correct row for max location_class.

Many thanks in advance!

<?php
// show all errors while debugging
error_reporting(E_ALL);
ini_set('display_errors', '1');

//set default timezone for date conversion
date_default_timezone_set('UTC');

$connection = mysql_connect ("localhost", "user", "pass");

if (!$connection) 
	{
	die('Not connecting to db server : ' . mysql_error());
	}
// Sets the active MySQL database.
$db_selected = mysql_select_db("satellite_data_example", $connection);

if (!$db_selected) {
	die('Can\'t use db : ' . mysql_error());
	}

// get a unique list of all PTT
$get_ptts = "
SELECT distinct ptt from birds 
ORDER BY ptt";
$result=mysql_query($get_ptts) or die ("Could not retrieve unique PTT numbers: ".mysql_error());
?>

<?php
// Itterates through the MySQL results, for each PTT.
while ($row = @mysql_fetch_assoc($result)) {

	// Var for unique PTT number of current bird
	$bird = $row['ptt'];
	print "Current ptt = " . $bird . "
	<br />
	";

	//get ptt, location_date, location_class, latitude, longitude for best row from each transmission for current bird during timewindow
	$get_best_locs = "
	SELECT MAX(location_class) AS id, location_class, ptt, latitude, longitude, location_date, FROM_UNIXTIME(location_date) FROM birds
	WHERE (ptt=$bird)
	and (location_class IN (1,2,3))
	and (location_date >= 1398072323)
	and (location_date <= 1398115003)
	GROUP BY YEAR(FROM_UNIXTIME(location_date)), MONTH(FROM_UNIXTIME(location_date)), DAY(FROM_UNIXTIME(location_date))";

		
	$result2=mysql_query($get_best_locs) or die ("Could not retrieve all best locations data: ".mysql_error());
	if (mysql_num_rows($result2)) {  // if one or more non-empty rows are returned..
		
			
		while ($row2 = @mysql_fetch_assoc($result2)) {
			    $locDateTimestamp = strtotime($row2["FROM_UNIXTIME(location_date)"]);
				print "ptt - " . $row2['ptt'] . "id - " . $row2['id'] . " class - ". $row2['location_class'] .  " timestamp - " . $row2['location_date'] . " (" . date('d.m.Y H:i:s e', $locDateTimestamp) . ") latlng - (" . $row2['latitude'] . "," . $row2['longitude'] . ")
				<br /><br />";
				
				}
		}
	}
?>
php-example.html
birds.sql
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 15 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 15 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros