分组依据并显示日期下的条目 [英] Group By and displaying entries under date

问题描述

这是我的数据:

id date  
1 2009-01-01 10:15:23  
2 2009-01-01 13:21:29  
3 2009-01-02 01:03:13  
4 2009-01-03 12:20:19  
5 2009-01-03 13:01:06 

我想做的是按每个日期分组，然后在每个日期下方列出ID号.我是否应该获取不同的日期值，然后循环使用它们以获取属于该日期的条目?这样有效吗?

What I'd like to do is group by each date and then list the id numbers below each date. Should I be getting the distinct date values and then cycling through them to get the entries that fall on the date? is this efficient?

所以我的输出看起来像:

so my output would look like:

2009-01-01  
1  
2   
2009-01-02  
3  
2009-01-03  
4  
5  

推荐答案

$query = "SELECT DATE(date) as mydate, id FROM your_data_table ORDER BY mydate, id";
$result = $pdo->query($query);

$oldDate = false;
while ( list($date, $id) = $result->fetch(PDO::FETCH_NUM) ) {
    if ( $oldDate != $date ) {
        echo "$date\n$id\n";
        $oldDate = $date;
    } else {
        echo "$id\n";
    }
}

您无需一次在多个查询中进行操作，而是一次获取所有数据.如果只需要一些日期，则只需在ORDER BY之前放置一个WHERE子句.

Instead of doing it in several queries, you just fetch all the data at once. If you only need some dates then you just put a WHERE clause before the ORDER BY.

这篇关于分组依据并显示日期下的条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！