MYSQL订购午夜时间表的PHP列车时刻表

Question

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 

Answer 1

If you want to put all times between midnight and 6am after other times, you can do:

ORDER BY timeref < '06:00', timeref