I recently needed to create a typical paginated directory page where people could browse via an alphabetical menu. So clicking ‘A’ returns all entries that begin with ‘A’, ‘B’ with ‘B’ and so on. CakePHP’s Paginator Helper made this all very easy.

However, there was one tricky requirement….in the menu, if there were no records that began with a corresponding letter, that letter should not be clickable. This meant before I displayed the contents of my directory page, I needed to get quite a bit of information about the database table…which letters never appear as the first letter of a record? With a table that stores 1000s of records, this could have added some dangerous overhead to this page.

So here’s my solution.

First, assume you have a ‘User’ model with a ‘name’ field, ‘users’ controller and ‘directory’ view that allows the user to browse all the users in the model. In your controller, your ‘directory’ action should look like this.

function directory ($filter = null) {
    $this->set('filter', $filter);

     // query all distinct first letters used in names
     $letters = $this->User->query('SELECT DISTINCT SUBSTRING(`name`, 1, 1) FROM `users` ORDER BY `name`');

     $links = array();
     // push all letters into a non-nested array
     foreach ($letters as $row) {
          array_push($links, current($row[0]));
     }

     $this->set('links', $links);

     $this->paginate['User'] = array(
     'limit' => 10,
     'order' => array(
          'User.name' => 'asc'
      )
);

$data = $this->paginate('User',
     array(
          'User.name LIKE' => $filter .'%'
     )
);

$this->set('data', $data);
$this->set('filter', $filter);

}

So what’s happening here….first, you are capturing the filter criteria, “A”, “B”, “C”, and storing it in a variable named $filter. Next, you want to modify a MySQL select statement using the MySQL text function SUBSTRING and the DISTINCT keyword. This will return an array of all the distinct letters used as the first letter in the ‘name’ field of the ‘users’ table. You want to extract the nested values into their own array and pass it to the view as ‘links’.

Next, you are setting up pagination for the view. You can learn more about the CakePHP’s pagination behavior here.

Your ‘directory’ view should include the following:

// set url arguements
$paginator->options(array('url' =>  array($filter)));

// render the previous link
echo $paginator->prev('prev',null,null,null);

// set up your alphabet
$alpha = range('A','Z');

for ($i=0; $i < count($alpha); $i++) {
	// if current letter is not in the links array, do not make it clickable
	if(!in_array($alpha[$i], $links)) {
		echo"<span class='nolink'>" . $alpha[$i] . "</span>";
	} else {
		if ($alpha[$i] == $filter) {
			// if the current letter matches the filter, give it a selected style
			echo $html->link($alpha[$i], array('controller' => strtolower($this->params['controller']), 'action' => 'directory', $alpha[$i]), array('class' => 'link_selected')) . "";
		} else {
			echo $html->link($alpha[$i], array('controller' => strtolower($this->params['controller']), 'action' => 'directory', $alpha[$i]), array('class'=>'link')) . "";
		}
	}
}
// render the 'next' link
echo $paginator->next('next', null, null, null);

foreach ($data as $row) {
		echo $row['User']['name'] . " [ " . $html->link('detail', array('controller' => strtolower($this->params['controller']), 'action' => 'detail', $row['User']['id'])) . " ]";
}

So here you are setting up the pagination controls and then looping through the $links array to see if the letter in your alphabet should be rendered dead, selected or clickable. You are also iterating through the array passed as ‘data’ and rendering a link to an action called ‘detail’ in your ‘users’ controller. You will need to build out your ‘detail’ action to display whatever details are associated with the User id. Notice you are also passing the id of the name associated with the link. You can add more logic as necessary.

This entry was posted on Tuesday, April 7th, 2009 at 1:10 pm and is filed under CakePHP. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.