CakePHP: COUNT data and GROUP BY date





Easily deploy an SSD cloud server on @DigitalOcean in 55 seconds. Sign up using this link and receive $100 in cloud credits: https://m.do.co/t/335732d1df0b





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')")
                                                        ));
?>

Comments

j4c3x said…
Awesome! Thank you, you made ma day