MYSQL Order for PHP train timetable after midnight
I am trying to order sets of train timetables by their departure times from stations, but the problem occurs when the train passes at midnight.
Below is an example of data going into my mysql database. For a given train schedule, I have a main route and main departure times from each station (indicated by the letter y under sche). From time to time, I may also receive updated departure times for the selected train schedule.
In its appearance, this is not a problem as I order my data based on timeref. However, my problem occurs when the train passes midnight. This is because the time I am getting is in a pure 24 hour format, i.e. 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 shown below (in the order shown). those. in order of the original schedule, but with the 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 when the 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: wrong
Luton t256 01:10
Leicester t256 02:00
London t256 22:10
Cricklewood t256 23:00
source to share
One trick you can use is to check the current time and order everything first timeref
after it (i.e. trains today) and only then timeref
before it (i.e. after 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 backlinks.
source to share