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,

Although possible in SQL, I think this is best handled at the application level. — Gordon Linoff
– Gordon Linoff, Commented May 23, 2014 at 22:11
If you want to do this purely is SQL you need GROUP BY and GROUP_CONCAT. you dont want to though. — dtech
– dtech, Commented May 23, 2014 at 22:16
@dtech Would groupConcat have any better performance? Or would I loose any advantage with the extra php processing? — fizzy drink
– fizzy drink, Commented May 23, 2014 at 22:31
@fizzydrink I highly doubt the performance difference is more than a few milliseconds until you reach 10.000 rows or something. If you want to be sure benchmark it. See my updated answer for how to do it. — dtech
– dtech, Commented May 24, 2014 at 9:39

John Conde · Accepted Answer · 2014-05-23 22:11:38Z

2

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

answered May 23, 2014 at 22:11

John Conde

220k100 gold badges464 silver badges504 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Victory Over a year ago

This is is a good answer but it assumes that the date column just stores the day, month and year, if not the $row['curDateCol'] will have to be transformed to the desired date string.

John Conde Over a year ago

Correct. This just solves their algorithm problem. They still need to format the date any way they wish. Since they didn't express any issues with that I did not addres it.

fizzy drink Over a year ago

@JohnConde Thanks. I will use this solution, although with a foreach since I'm dealing with an object array. But it works!!! Also, I might try the groupConcat in the future.

Fluffeh · Accepted Answer · 2014-05-23 22:16:11Z

0

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.

answered May 23, 2014 at 22:16

Fluffeh

33.6k16 gold badges69 silver badges80 bronze badges

4 Comments

dtech Over a year ago

The function is GROUP_CONCAT right? And I don't think PHP will foreach over a CSV string.

Fluffeh Over a year ago

@dtech No, but as you can put the delimiters in, you can explode it into an array with ease.

dtech Over a year ago

So you let MySQL collapse a list into a string, and then let PHP expand a string into a list. Doesn't seem the proper course of action.

Fluffeh Over a year ago

@dtech Why not? Why bring back all that data just to then ignore it and only use a single column of what you got?

dtech · Accepted Answer · 2014-05-24 09:47:31Z

0

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.

edited May 24, 2014 at 9:47

answered May 23, 2014 at 22:13

dtech

14.2k11 gold badges52 silver badges73 bronze badges

Collectives™ on Stack Overflow

php / mysql - Sort by date

3 Answers 3

3 Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related