Grouping items from mysql by the day they were posted

Question

So, I'm having a problem building a feed-style list of posts efficiently using PHP and Mysql. I want to output the posts organized and grouped by the day they were posted, showing the date as a header. For example:

February 9th -----------

Posts that were posted on Feb 9th

Feb 8th -----------------

Posts that were posted on the 8th

Hopefully that is clear. I'll try to clear up things if someone is confused.

Answer 1

The tag in the question hints at using "GROUP BY". You do not need the SQL GROUP BY construct. Instead, the query should look something like :

SELECT *
FROM MyPostsTable
--WHERE  some optional condition
ORDER BY PostingDate -- DESC  (if you want the newer dates first)

Then as you iterate through the resuults list, in PHP, you simply need to keep tabs of the date of the items currently being output, and emit a header of sort ("February 9th ----------"), at the start of a new date.

Edit: see Matt Bridges' answer for a sample implementation of the logic described above.

Answer 2

$sql = "SELECT PostDataColumn1, PostDataColumn2, date_format('%Y-%m-%d', DateColumn) FROM posts_table ORDER BY DateColumn DESC";
$result = mysql_query($sql);
$curDate = "";    

while (list($col1, $col2, $date) = mysql_fetch_row($result))
{
  if ($date != $curDate)
  {
    echo "$date --------\n";
    $curDate = $date;
  }

  echo "Post data: $col1 $col2";
}