PHP/MySQL: Sort by time, then by date

Question

I've stumbled onto an issue I thought would be easy to resolve, but seems to be driving me crazy. So, I'm trying to sort some MySQL records by time, and then sort of "group" them by date. For example, here's my MySQL data:

+----+------------+----------+---------------------------+--------+
| id | date       | time     | entry                     | status |
+----+------------+----------+---------------------------+--------+
| 21 | 2011-10-05 | 09:42:06 | All systems online.       |      1 |
| 22 | 2011-10-05 | 09:43:09 | Maintenance starting.     |      2 |
| 23 | 2011-10-04 | 08:42:06 | Systems online and ready. |      1 |
| 24 | 2011-10-05 | 09:44:30 | Systems are offline.      |      0 |
+----+------------+----------+---------------------------+--------+

So, the query I use to get everything sorted is:

SELECT * FROM status order by date ASC;

Which yields the following results:

+----+------------+----------+---------------------------+--------+
| id | date       | time     | entry                     | status |
+----+------------+----------+---------------------------+--------+
| 21 | 2011-10-05 | 09:42:06 | All systems online.       |      1 |
| 22 | 2011-10-05 | 09:43:09 | Maintenance starting.     |      2 |
| 24 | 2011-10-05 | 09:44:30 | Systems are offline.      |      0 |
| 23 | 2011-10-04 | 08:42:06 | Systems online and ready. |      1 |
+----+------------+----------+---------------------------+--------+

The PHP output is the issue. So, the output RIGHT NOW is:

October 4, 2011

• Systems online and ready. [08:42 AM]

October 5, 2011

• All systems online. [09:42 AM]

• Maintenance starting. [09:43 AM]

• Systems are offline. [09:44 AM]

What I WANT the output to be:

October 5, 2011 - Systems are offline. [09:44 AM]

• Maintenance starting. [09:43 AM]

• All systems online. [09:42 AM]

October 4, 2011

• Systems online and ready. [08:42 AM]

Basically, I'm wanting everything grouped by date (latest first) and I want the most recent time at the top, not the bottom.

Here is my PHP code:

function getUpdates() {
    global $db;
    $updchk = "";
    $entries = $db->GetAll("SELECT * FROM status order by date DESC;");
    if (!$entries) { ?>
        <p>No entries in the database, yet.</p>
  <?php } else
    foreach ($entries as $entry) {
        if (ConvertDate($entry['date']) != $updchk) { ?>
            <h4><?php echo ConvertDate($entry['date']); ?></h4>
            <p><?php echo $entry['entry']; ?><span class="timestamp"> [<?php echo strftime('%I:%M %p', strtotime($entry['time'])); ?>]</span></p>
            <?php $updchk = ConvertDate($entry['date']); ?>
        <?php } else { ?>
            <p><?php echo $entry['entry']; ?><span class="timestamp"> [<?php echo strftime('%I:%M %p', strtotime($entry['time'])); ?>]</span></p>
        <?php }
    } ?>
<?php } ?>

Any help is appreciated.

Thanks!

Solution 1

just add an extra clause to the ORDER BY?

SELECT ...
FROM status
ORDER BY `date` DESC, `time` DESC

You can sort on as many (or few) fields as you want, even on arbitrary expressions if need be.

Solution 2

change your query to

SELECT * 
FROM status 
order by `date` desc, `time` desc;

Solution 3

try this:

SELECT * FROM `status` ORDER BY `date`, `time` DESC 

Solution 4

To order by date then time in SQL,

SELECT * FROM status ORDER BY date DESC, time DESC;

Solution 5

SELECT * FROM `status` ORDER BY `date` DESC, `time` DESC

will work for your code

View more solutions

Comments

[Wh1T3h4Ck5]

tell us please, what's difference between your answer and one already accepted, correct answer, posted YEAR AGO!!!