[SOLVED] displaying by date and grouping by day

[scarhand]

im trying to display results from my database by date and group them together by day.

 

this is what the mysql table looks like:

 

title | date

test title 4 | 2008-01-11 05:14:56

test title 3 | 2008-01-11 05:14:56

test title 2 | 2008-01-10 05:14:56

test title 1 | 2008-01-10 05:14:56

 

and i want the results to look like this:

 

january 11, 2008

 

test title 4

test title 3

 

january 10, 2008

 

test title 2

test title 1

 

i have been messing around with this for a while and cant get it to work the way i want it to. you can probably do this in 1 mysql statement but heres the coding i have so far:

 

<?php

$sql1 = mysql_query("SELECT * FROM videos GROUP BY date");
$sql2 = mysql_query("SELECT * FROM videos ORDER BY date DESC");
$count = mysql_num_rows($sql1);

if ($count == 0)
{
  echo 'There are no videos to display';
}
else
{
  while ($row = mysql_fetch_array($sql1))
  {
    $date = date("M j, Y", strtotime($row['date']));
  
    echo "$date<br><br>";
    
    while ($row = mysql_fetch_array($sql2))
    {
      $title = $row['title'];

      echo "$title<br>";
    }
  }
}

?>

 

any help is greatly appreciated

[toplay]

Use one query that uses the "ORDER BY". You can also use DATE_FORMAT() to return the date the way you want. Return one format for displaying and return another in YYYYMMDD format so it can be used to compare.

 

Then as you read each row have PHP logic where you check when the date has changed. Each time the date changes, you display the title and keep track of the new date so you can compare it to the next row (to see if it's changed).