mysql - CakePHP convert datetime to date and group results -

mysql - CakePHP convert datetime to date and group results -

i have table of reports , want grouping them created date. each has created field datetime.

i tried doing this:

$reports = $this->report->find('all', array( 'recursive' => 0, 'group' => 'date(created)' ));

which returned right amount of groups (3 because have many records 3 different dates @ moment), returns 1 record per group. answered @kai sql grouping homecoming 1 unique record.

i want display table of results table header separating results each grouped date. best cakephp way of achieving this?

i think you're getting concepts of group by , order by in mysql confused.

group by

group by makes sense in contexts. example, if had table contained products had category , price, , wanted product maximum cost each category. if utilize select max(price) products, you'll single expensive product in table. that's group by comes in: select max(price) products grouping category_id. group by 1 row per unique value in column specified in group by. in example, means group by, it'll 1 row per category id, i'll able see max cost each category.

order by

order by pretty self explanatory -- order results column have specified.

so, getting question, should using order by, not group by.

$reports = $this->report->find('all', array( 'recursive' => 0, 'order' => 'date(created)' ));

eta: creating table of results, each date separated table header, proceed if going create regular table without separators, save date in first row in temporary variable. every iteration, check temporary variable against row's date. if row's date different, insert header, , update temporary variable current row's date. here's psuedo-code help clarify:

echo '<table>'; //echo first set of headers. $temp = $report[0]['report']['created']; foreach($data $report) { if($temp != $report['report']['created']) { //echo headers $temp = $report['report']['created']; } //echo row of info here. } echo '</table>';

mysql sql cakephp datetime