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...
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;
}
}