Sort tinytext time from mysql db query with sql or sort the array it produces using PHP

Question

Hey guys - The problem stems from a poorly designed database used to store real estate information. I set up a template for my client to select a weekend and to display the open houses for that weekend. Open house times (ohtime1, ohtime2, ohtime3) are stored as tinytext, with no way of knowing AM or PM. "12:00 - 2:00" and "01:00 - 03:00" are common entries that we humans discern as noon-2pm and 1pm-3pm, however when I query the database and ORDER BY ohtime1, it obviously puts 01:00 before 12:00. I am having difficulty sorting using SQL and using the different php sort methods. The initial listings array with all the open house information is set up like something as follows:

$listings[0][displayaddress] = empire state building
$listings[0][baths] = too many to count
$listings[0][ohtime1] = 12:00 - 02:00

$listings[1][displayaddress] = madison square garden
$listings[1][baths] = 2
$listings[1][ohtime1] = 01:00 - 03:00
etc...

I iterate through $listings with foreach($listings as $listing) to process for the smarty templates we use, as well as to separate into the different days, and then again for manhattan and brooklyn listings. This results in 4 new arrays. My theory was if I convert all the times before 09:00am to 24 hour time, then sort them, then assign to the different day/borough it would work. Here is the converting code:

$p = explode("-",$listing[ohtime1]);  //01:00 - 03:00
 $time1 = trim($p[0]); //01:00
 $time2 = trim($p[1]); //03:00
 $hour1 = substr($time1,0,2);  //01
 $hour2 = substr($time2,0,2);  //03
 $min1 = explode(":",$time1);  
 $min2 = explode(":",$time2);
 $min1 = $min1[1]; //00
 $min2 = $min2[1]; //00

 //convert all times to 24 hour
 if($hour1 < 9) $hour1 = $hour1+12; //13
 if($hour2 < 9) $hour2 = $hour2+12; //15
 $listing[ohtime1] = $hour1.":".$min1." - ".$hour2.":".$min2; //13:00 - 15:00
 $listing[hour1] = $hour1;
 $listing[hour2] = $hour2;

Converting wasn't difficult, but am at a loss as to how to sort them. I am not versed in advanced SQL theory as to implement the conversion to 24hrs I did in php into mysql. I was also thinking I could implement a sorting feature when I create the new arrays but I am again at a loss. Here is the code for separating into the new arrays:

foreach($openhouse_date_fields as $oh){ //3 possible open house dates  
if(substr($listing[$oh], 0,10) == $date) {  //if any of the listings's open houses match the first search date  
    if($listing[sect] == "Brooklyn") {  
       $listingsb[$listing[displayaddress]] = $listing;  
    }  
    else  
       $listingsm[$listing[displayaddress]] = $listing;  
}  
elseif(substr($listing[$oh], 0,10) == $date2) { //if any of the listings's open houses match the second search date  
      if($listing[sect] == "Brooklyn")  
   $listingsb2[$listing[displayaddress]] = $listing;  
      else  
    $listingsm2[$listing[displayaddress]] = $listing;  
}  
}

I hope that is enough information. Thanks for taking the time to read and for any feedback!

Answer 1

Here's an example of converting one of the TINYTEXT columns to a pair of columns of type TIME:

SELECT
 MAKETIME(start_hour  + IF(start_hour<9, 12, 0), start_minute, 0) AS start_time,
 MAKETIME(finish_hour + IF(start_hour<9, 12, 0), finish_minute, 0) AS finish_time
FROM (
 SELECT
  SUBSTRING_INDEX(SUBSTRING_INDEX(ohtime1, ' - ',  1), ':',  1) AS start_hour,
  SUBSTRING_INDEX(SUBSTRING_INDEX(ohtime1, ' - ',  1), ':', -1) AS start_minute,
  SUBSTRING_INDEX(SUBSTRING_INDEX(ohtime1, ' - ', -1), ':',  1) AS finish_hour,
  SUBSTRING_INDEX(SUBSTRING_INDEX(ohtime1, ' - ', -1), ':', -1) AS finish_minute
 FROM MyOpenHouseTable) t
ORDER BY start_time;