CODEWITHSUNDEEP
phpmysqlsqlsorting
drewrockshard
drewrockshard

Reputation: 2071

PHP/MySQL: Sort by time, then by date

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

October 5, 2011

What I WANT the output to be:

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

October 4, 2011

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!

Upvotes: 5

Views: 21457

Answers (6)

Manoj Patil
Manoj Patil

Reputation: 11

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

will work for your code

Upvotes: 1

Nikhil
Nikhil

Reputation: 68

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

Upvotes: 0

JellyBelly
JellyBelly

Reputation: 2431

try this:

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

Upvotes: 3

Paul Stanley
Paul Stanley

Reputation: 4098

To order by date then time in SQL,

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

Upvotes: 3

Adriano Carneiro
Adriano Carneiro

Reputation: 58595

change your query to

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

Upvotes: 4

Marc B
Marc B

Reputation: 360642

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.

Upvotes: 18

Related Questions