PHP max number of days in a pool of dates

Hey guys, Im trying to get the maximum number of days in a database of days. For instance, a database of (Sat, Mon, Tue, Mon, Tue, Fri, Thu, Sun, Mon) Should print "Mon" as the result (Being the maximum number of occurance). But im stuck!! The database is weird. Have a look.

time_id           time_duration

 1              2010-10-15 10:05:00
 1              2010-10-15 10:10:00
 1              2010-10-15 10:15:00
 2              2010-10-16 10:20:00
 2              2010-10-16 10:25:00
 2              2010-10-16 10:30:00
 3              2010-10-17 10:35:00
 3              2010-10-17 10:40:00
 3              2010-10-17 10:45:00
 4              2010-10-18 10:50:00
 4              2010-10-18 10:55:00
 4              2010-10-18 11:00:00
 4              2010-10-18 11:05:00
 4              2010-10-18 11:10:00
 5              2开发者_如何学编程010-10-19 11:15:00
 5              2010-10-19 11:20:00
 5              2010-10-19 11:25:00

So we need to group the time_id first to get the start_time and end time. Result should be:

  1: "Start 2010-10-15 10:05:00  -  End 2010-10-15 10:15:00"
  2: "Start 2010-10-16 10:20:00  -  End 2010-10-16 10:30:00"
  3: "Start 2010-10-17 10:35:00  -  End 2010-10-17 10:45:00"
  4: "Start 2010-10-18 10:50:00  -  End 2010-10-18 11:10:00"
  5: "Start 2010-10-19 11:15:00  -  End 2010-10-19 11:25:00"

And then we get the most common day of the week. So, amusing the most common is Monday, The result should be Monday.

Here is what I have done so far...

  $dates = mysql_query("SELECT DATE_FORMAT(MAX(time_duration), '%a') 
                          AS max_days FROM timeDuration 
                          GROUP BY time_id
                          ORDER BY time_id");

  while($row = mysql_fetch_array($dates)) {
  $maxDayOfSign = $row['max_days'];}?>

Could you please direct me to where im going wrong? I get no errors when i display it using echo $maxDayOfSign; but it doesn't display anything. Help!! :(

---

Try this query:

SELECT DATE_FORMAT(`time_duration`, '%a') AS `max_days`,
    COUNT(*) AS `day_count`
FROM `timeDuration`
GROUP BY `max_days`
ORDER BY `day_count` DESC
LIMIT 1

---

EDIT - Oops, wasn't familiar with TIME_FORMAT's %a. You probably dont want it but if you're inclined to do more of the work in PHP see below.

I'm not sure how you're going to go from date to do of the week in SQL. I'm not sure if this can be done, and if it is, im sure it is dependent on you're database (you didnt mention - MySQL, MS, Oracle, PostGre?)

I'd suggest you do this on the PHP side with mktime() and date(). Get your list of dates in simple array, then

function dateToDayOfWeek($y, $m, $d)
{
   return date('D', mktime($y, $m, $d));
}

function dayOfWeekCounter($dateArr)
{
    $arr = array('Mon' => 0, 'Tue' => 0, ...., 'Sun' => 0);
    foreach ($dateArr as $curDate)
    {
          $y = //you worry about parsing
          $m = //
          $d = //
          $arr[dateToDayOfWeek($y,$m$d)]++;
    }
    return $arr
}