按日期显示MySQL结果 [英] Display MySQL results by date

问题描述

我有这个mysql表:

I have this mysql Table:

+--------------------+---------+-------+
|      date          | query   | count |
|--------------------+---------+-------|
|2012-11-18 09:52:00 | Michael |   1   |
|2012-11-18 10:47:10 |  Tom    |   2   |
|2012-11-17 15:02:12 |  John   |   1   |
|2012-11-17 22:52:10 |  Erik   |   3   |
|2012-11-16 09:42:01 |  Larry  |   1   |
|2012-11-16 07:41:33 |  Kate   |   1   |
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

，依此类推.我可以简单地获取结果并通过以下代码按日期将其排序:

and so on. I can simply take results and order them by date in one row via this code:

$queries = mysql_query("SELECT * FROM my_tables ORDER BY date DESC LIMIT 20"); 
while($row = mysql_fetch_array($queries)){
    echo "Name ".$row['query']."";
}

但是如何显示按特定日期排序的表中的元素，如下所示:

But how to display elements from table ordered by specific date like this:

In 2012-11-18:
Michael
Tom

In 2012-11-17:
John
Erik

In 2012-11-16:
Larry
Kate

，依此类推.谢谢！

推荐答案

这是PHP代码:

$query = mysql_query("SELECT date, query FROM table6 ORDER BY date DESC LIMIT 20");
$group_date = null;
while ($row = mysql_fetch_assoc($query)) {
    if ($group_date !== substr($row["date"], 0, 10)) {
        $group_date = substr($row["date"], 0, 10);
        echo "<h1>$group_date</h1>\n";
    }
    echo "${row['query']}<br>\n";
}

输出:

2012-11-18

汤姆

2012-11-18

Tom

迈克尔

Erik

约翰

拉里

凯特

请注意，尽管此代码将行按一列分组"，但可以轻松扩展为按多列对行进行分组.留做练习.

Note that while this code "groups" rows by one column, it can easily be extended to group rows by multiple columns. Left as an exercise.

这篇关于按日期显示MySQL结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！