SQL Group By Date But Maintain Records

Question

I assume the php/cakephp/sql parliance. I have a History table which contains the column created of type DATETIME. I would show all the records of the History but arranged by day.

------------------------------------------
id   created                what
------------------------------------------
1    2014-01-01 08:00:00   Someone ran
2    2014-01-01 09:00:00   Someone cried
2    2014-01-02 10:00:00   Someone spoke
2    2014-01-02 18:00:00   Someone worked

And the result I am looking for

array(
'2014-01-01' => array(
                      0=>array('time'=>'8:00','what'=>'Someone ran'
                      1=>array('time'=>'9:00','what'=>'Someone cried'
                     ),
'2014-01-02' => array(
                      0=>array('time'=>'10:00','what'=>'Someone spoke',
                      1=>array('time'=>'18:00','what'=>'Someone worked',
                     )
)

I am totaly stucked. Which SQL statement should I investigate? GROUP is not good...

This can't be done in a single sql statement. You can run two different sql statement to fetch the groups seperately. Otherwise, you have to do this in php. — Vulcronos, Jul 29 '14 at 17:14
I don't know php I am afraid. If this was c# you could run a sql query to get all records, then use linq to group on the date. Hopefully someone in the php tag knows how to do that. — Vulcronos, Jul 29 '14 at 17:17
possible duplicate of [Grouping arrays in PHP](http://stackoverflow.com/questions/982344/grouping-arrays-in-php) — Jay Blanchard, Jul 29 '14 at 17:24
have you looked into order by? Correct me if I'm wrong, but it looks like you are just sorting the data by the created date in chronological order. — Jenn, Jul 29 '14 at 19:08
The problem is that after sorting I have to "group" although this word is misleading, beacuse actually I want to make groups not "to group". — gdm, Jul 30 '14 at 07:16
@Blanchard It is not a duplicate but a variation on the theme. If this is annoying let me know and I'll cancel the question. — gdm, Jul 30 '14 at 07:34

score 0 · Answer 1 · edited May 23 '17 at 12:11

If the other reviewers agree, I would give my answer in cakephp adapted from this SO answer

 $histories = $this->History->find('all',
                    array(
                        'group'=>array('History.created')
            ));


 foreach ($histories as $item) {
                $key = date('Y-m-d',strtotime($item['History']['created']));
                if (!isset($groups[$key])) {
                    $groups[$key] = array(
                        'items' => array($item),
                        'count' => 1,
                    );
                } else {
                    $groups[$key]['items'][] = $item;
                    $groups[$key]['count'] += 1;
                }
            }

SQL Group By Date But Maintain Records

1 Answers1