Reputation:
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. 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
Upvotes: 1
Views: 526
Reputation: 1961
$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";
}
}
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
.
Upvotes: 3
Reputation: 827198
Your stored dates have time information (the type of the column is DATETIME or TIMESTAMP), in order to do a group by, you need to extract only the date part your timespan, you can use the DATE() function.
Upvotes: 2