I am trying to order sets of train timetables by their departure times from stations but the problem occurs when a train passes over midnight.
Below is an example of the data flowing into my mysql database. For a given train schedule I have its basic route and basic departure times from each station (Marked by the y under sche). Every now and then I may also receive an update of amended departure times for a given train schedule.
In its self the above is not a problem as I order my data based on the timeref. However my problem comes when a train passes midnight. This is because the times I receive are in pure 24hr format - ie 01:00
Count Location Trainid Timeref Sche Update
1 London t256 22:10 y
2 Cricklewood t256 23:00 y
3 Luton t256 01:00 y
4 Leicester t256 02:00 y
5 Doncaster r659 06:00 y
6 Luton t256 01:10 y
7 Sheffield y783 05:00 y
I want to show the data as below (in that order). ie in the order of the original timetable but with an updated time for Luton to 01:10
London t256 22:10
Cricklewood t256 23:00
Luton t256 01:10
Leicester t256 02:00
My below code works fine except where a train crosses midnight
$stmt3=$mysql_link->prepare("SELECT count,trainid,location,timeref,sche,update FROM (SELECT count,trainid,location,timeref,sche,update FROM darwinall WHERE trainid=:trainid ORDER BY count DESC) as temp GROUP BY location ORDER BY timeref ASC");
$stmt3->execute(array(':trainid'=>$trainid));
foreach($stmt3->fetchAll(PDO::FETCH_ASSOC) as $row3) {
echo result.................
}
So instead of the above I get the following result, which is wrong
Luton t256 01:10
Leicester t256 02:00
London t256 22:10
Cricklewood t256 23:00
If you want to put all times between midnight and 6am after other times, you can do:
ORDER BY timeref < '06:00', timeref
One trick you could use is to to check the current time, and first order all the timerefs after it (i.e., today's trains) and only then the timerefs before it (i.e., post-midnight):
SELECT `count`, trainid, location, timeref, sche, `update`
FROM (SELECT `count`, trainid, location, timeref, sche, `update`
FROM darwinall
WHERE trainid=:trainid
ORDER BY `count` DESC) AS temp
GROUP BY location
ORDER BY timeref < CURRENT_TIME() ASC, timeref ASC
BTW: count and update are reserved words, you should probably protect them with backticks.