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!

Marc B · Accepted Answer · 2011-10-05 15:14:13Z

18

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.

answered Oct 5, 2011 at 15:14

Marc B

362k44 gold badges433 silver badges508 bronze badges

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

Comments

Adriano Carneiro · Accepted Answer · 2011-10-05 15:14:41Z

4

change your query to

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

answered Oct 5, 2011 at 15:14

Adriano Carneiro

58.8k12 gold badges94 silver badges123 bronze badges

Comments

Paul Stanley · Accepted Answer · 2011-10-05 15:15:27Z

3

To order by date then time in SQL,

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

answered Oct 5, 2011 at 15:15

Paul Stanley

4,0966 gold badges39 silver badges56 bronze badges

Comments

JellyBelly · Accepted Answer · 2011-10-05 15:19:13Z

3

try this:

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

answered Oct 5, 2011 at 15:19

JellyBelly

2,4312 gold badges21 silver badges42 bronze badges

Comments

Tisho · Accepted Answer · 2012-07-27 11:25:45Z

1

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

will work for your code

edited Jul 27, 2012 at 11:25

Tisho

8,5426 gold badges46 silver badges52 bronze badges

answered Jul 27, 2012 at 8:41

Manoj Patil

112 bronze badges

1 Comment

Wh1T3h4Ck5 Over a year ago

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

Nikhil · Accepted Answer · 2011-10-05 15:26:25Z

0

SELECT * FROM status order by DATE(date) asc,time desc;

answered Oct 5, 2011 at 15:26

Nikhil

681 silver badge8 bronze badges

Collectives™ on Stack Overflow

PHP/MySQL: Sort by time, then by date

6 Answers 6

Comments

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related