CakePHP: COUNT data and GROUP BY date






Goal: Count Tip Offs created per day for a month to use for graph
Problem: created field name is in datetime format: Y-m-d H:i:s
Solution: format SQL Query date: DATE_FORMAT(TipOff.created, '%Y-%m-%d')





Inside the function of controller

<?php
$tipOffsMonthly = $this->TipOff->find('all', array('conditions'=>array('AND'=>array(
                                                                'TipOff.electric_cooperatives_id'=>AuthComponent::User('electric_cooperatives_id'),
                                                                'TipOff.created BETWEEN ? AND ?'=>array($first_day, $last_day)
                                                                )),
                                                        'fields'=>array("COUNT('created') as per_day", 'created'),
                                                        'group'=>array("DATE_FORMAT(TipOff.created, '%Y-%m-%d')")
                                                        ));
?>
1 comment

Popular posts from this blog

CakeAdminLTE is based on AdminLTE Theme for CakePHP 2.4+

CakePHP: Calling function from other controller