CODEWITHSUNDEEP
mysqlsqlcakephpdatetime
Ronny vdb
Ronny vdb

Reputation: 2474

CakePHP convert datetime to date and group results

I have a table of reports and I want to group them by the created date. Each has the created field which is datetime.

I tried doing this:

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

which returned the correct amount of groups (3 because I have many records but only 3 different dates at the moment), but it only returns one record per group. As answered by @Kai sql grouping will also return the one unique record.

I want to display a table of the results with a table header separating the results for each grouped date. What would be the best CakePHP way of achieving this?

Upvotes: 0

Views: 770

Answers (1)

Kai
Kai

Reputation: 3823

I think you're getting concepts of GROUP BY and ORDER BY in mysql confused.

Group By

GROUP BY only makes sense in certain contexts. For example, if you had a table that contained products that had a category and a price, and you wanted to get the product with the maximum price for each category. If you just use SELECT MAX(price) FROM products, you'll get the single most expensive product in your table. That's where GROUP BY comes in: SELECT MAX(price) FROM products GROUP BY category_id. GROUP BY will always get one row per unique value in the column you specified in GROUP BY. In my example, that means with GROUP BY, it'll get one row per category id, so I'll be able to see the max price for each category.

Order By

ORDER BY is pretty self explanatory -- it will order your results by the column you have specified.

So, getting back to your question, you should be using ORDER BY, not GROUP BY.

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

ETA: As for creating a table of your results, with each date separated by a table header, proceed as if you were going to make a regular table without those separators, but save the date in the first row in a temporary variable. Every iteration, check your temporary variable against that row's date. If the row's date is different, insert your header, and update your temporary variable to the current row's date. Here's some psuedo-code to help clarify:

echo '<table>';
//echo your first set of headers.

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

Upvotes: 1

Related Questions