Dealing with dates… reporting, etc.

Once in a while I run into a situation, where some sort of reporting is required for an application…
(It happens.)

An example?.. We need to get a list of all active users in the DB.

Obviously CakePHP makes it overly easy for us.
However, it also happens, that once in a while we need to filter the list by using a “from” and “to” date range.

I really wanted to keep things simple and re-usable (yeah, lazy) and one solution came to mind…

It fits nicely within an MVC concept, and since I’ve got nothing else to write about, perhaps it might be worth sharing.

To get things started, we’ll consider a view:

  echo $form->create('User', array('action' => 'report_to_me'));

  echo $form->input('active_from');
  echo $form->input('active_to');

  echo $form->end('Set date');

<?php if(!empty($allActiveUsers)) : ?>

  <?php foreach($allActiveUsers as $user) : ?>

      <?php echo $user['User']['name']; ?>

  <?php endforeach; ?>

<?php else : ?>

    Ain't got no active users.

<?php endif; ?>

Pretty straight-forward… We display a user’s name for all active users from the DB.
Also, there is a way for someone to input a date range and send it off to the controller.

Let’s take a look at the action in the controller:

public function report_to_me() {
      if(!empty($this->data)) {
        $this->User->fromDate = $this->data['User']['active_from'];
        $this->User->toDate = $this->data['User']['active_to'];

      $this->set('allActiveUsers', $this->User->getActive());

If no date is supplied (i.e. $this->data is empty) we show all active users from the DB.
Yet, if the date is supplied we set a few properties in our User model…
You might notice that I like fat models (eh?), therefore the goal is, once again, to avoid any logic in the controller.

With that being said, we’ll take a look at the User model:

    class User extends AppModel {

      public $fromDate = NULL;
      public $toDate = NULL;

      public function getActive() {

        return $this->find('all', array('conditions' => array('User.is_active' => 1,
                                 'recursive' => -1));

      private function getDateConditions() {
        if(!empty($this->fromDate) && !empty($this->toDate)) {
          return array($this->alias . '.' . 'created BETWEEN ? AND ?' =>
                 array(date('Y-m-d', strtotime($this->fromDate)),
                       date('Y-m-d', strtotime($this->toDate))));

        return FALSE;

The only interesting piece of code here is the getDateConditions() method.
Once we set the $fromDate and $toDate properties, the method gives us a nicely formatted condition for the date range. Otherwise, as promised, we get a list of all active users (no date condition is applied).

Related Posts