PHP/MySQL:按时间排序，按日期排序

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:

我无意中发现了一个我认为很容易解决的问题，但似乎让我发疯。我试着按时间排序一些MySQL记录，然后按日期排序。例如，这是我的MySQL数据:

+----+------------+----------+---------------------------+--------+
| 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;

按日期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:

PHP输出就是问题所在。现在的输出是:

October 4, 2011

2011年10月4日

Systems online and ready. [08:42 AM]
系统在线和准备好了。(上午08:42)

October 5, 2011

2011年10月5日

All systems online. [09:42 AM]

所有系统在线。(09:42点)
Maintenance starting. [09:43 AM]

维护开始。(09:43点)
Systems are offline. [09:44 AM]

系统是离线。(09:44点)

What I WANT the output to be:

我想要的输出是:

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

2011年10月5日——系统离线。(09:44点)

Maintenance starting. [09:43 AM]

维护开始。(09:43点)
All systems online. [09:42 AM]

所有系统在线。(09:42点)

October 4, 2011

2011年10月4日

Systems online and ready. [08:42 AM]
系统在线和准备好了。(上午08:42)

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:

这是我的PHP代码:

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!

谢谢!

6 个解决方案

#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.

您可以根据需要对任意多个(或几个)字段进行排序，甚至在需要时也可以对任意表达式进行排序。

#2

change your query to

改变你的查询

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

#3

To order by date then time in SQL,

按照SQL的日期顺序，

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

#4

try this:

试试这个:

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

#5

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

will work for your code

对你的代码有效吗

#6

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

按日期(日期)asc、time desc选择* FROM status order;

#1