php / mysql - Sort by date

Question

Ok,

So I have a table with various columns, one of them being a date column that I use to sort the latest entries to the database, such as: SELECT * FROM foo ORDER BY date DESC LIMIT 0,25. This gives me the 25 latest entries.

What I want, is to group all the entries of one date together in the html such that the output will be:

<li class="date">Date 1.2.2014</li>
<li>Some Entry</li>
<li>Some Entry</li>
<li>Some Entry</li>
<li>Some Entry</li>
<li class="date">Date 1.1.2014</li>
<li>Some Entry</li>
<li>Some Entry</li>

The containers are inconsequential, they can be <li>, <td> whatever.

I would like to know whether I can do this in MYSQL with some sort of query or what PHP logic I would need to get the result in such a way.

Also, it should be scalable, in other words, if I want the latest 50, 100, 1000 records, all that needs to change is the LIMIT range in the query. The output will automatically 'parse' I guess the result and add a date heading every time a new date is encountered.

Thanks,

Answer 1

Just keep track of the date. If it changes, output the new date.

// The current date. Start with nothing.
$currentDate = '';
// Loop through your db results
while ($row = mysql_fetch_assoc($result)) {
    // Check to see if the date of the current row is the same as previous row. 
    if ($currentDate !== $row['yourDateCol']) {
        // echo out date
        echo '<li class="date">'.$row['yourDateCol'].'</li>';
        // save the current date in $currentDate to cehck in next loop iteration
        $currentDate = $row['yourDateCol'];
    }
    // echo out event details
    echo '<li>'.$row['yourEventCol'].'</li>';
}