Group By and displaying entries under date and display count

Here's my data:

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 

What I'd like to do is group by each date and then list the id numbers below each date. I also want to list the count of ids per date.

so my output would look like:

2009-01-01  - Count:2
1  
2   
2009-01-02  - Count:1开发者_StackOverflow
3  
2009-01-03  - Count:2
4  
5  

I have the following code that works, but need help changing the logic to display the count.

$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";
    }
}

If you don't mind letting the DB do all the job, and maybe parse the result, an easy way to do is:

SELECT DATE(date) as mydate, COUNT(id) as num, GROUP_CONCAT(id SEPARATOR ",") as ids
FROM your_data_table
GROUP BY DATE(date)
ORDER BY mydate

Maybe needs some work (hadn't tested it).

For the group totals to be done automatically by MySQL, you'd need to use the ROLLUP option. But the query results would still need some client-side massaging to properly display the appropriate divisions when switching from one group to the next.

you can do something like this :

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

$datas = array();
while(list($date, $id) = $result->fetch(PDO::FETCH_NUM)) {
  if(! isset($datas[$date]) $datas[$date] = array();
  $datas[$date][] = $id;
}

foreach($datas as $date => $ids} {
    echo $date.' - Count: '.count($ids)."\n";
    foreach($ids as $id) {
        echo $id."\n";
    }
}