CODEWITHSUNDEEP
phpmysqlsqlsortingdate-sorting
fizzy drink
fizzy drink

Reputation: 682

php / mysql - Sort by date

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,

Upvotes: 0

Views: 735

Answers (3)

dtech
dtech

Reputation: 14060

Remember the last date

$cur = null;
while($row = mysql_fetch_assoc($r)) {
    if($row['date'] !== $cur) {
        $cur = $row['date'];
        echo "<li class="date">" . $row['date'] . "</li>\n";
    }
    echo "\t<li>" . $row['event'] . "</li>\n";
}

Or do more in SQL with GROUP CONCAT:

// SELECT date, GROUP_CONCAT(event SEPARATOR '</li><li>') AS entries FROM foo ORDER BY date DESC GROUP BY date LIMIT 25
while($row = mysql_fetch_assoc($r)) {
    echo "<li class="date">" . $row['date'] . "</li><li>" . $row['entries'] . "</li>";
}

Note that entries will be truncated to 1024 characters, so it is unsuitable if you have a lot of events per date. I would also recommend against doing this.

Upvotes: 0

Fluffeh
Fluffeh

Reputation: 33512

You can do this via a group concat in the query like this (though it would generally work better for things like IDs:

select yourDateField, groupConcat(id) 
from someTable order by yourDateField desc limit 0,25

which would then return data like:

2014-05-22
1, 2, 3, 4, 5

for each row which you can then easily explode in a foreach statement.

Upvotes: 0

John Conde
John Conde

Reputation: 219814

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>';
}

Upvotes: 2

Related Questions