A recent issue I've run into is trying to use CodeIgniter's pagination class with queries that contain WHERE conditions. Specifically, I need to set the paginator's $config['total_rows'] to the number of rows in the full result set when one or more WHERE conditions exist in conjuction with a LIMIT clause. And ideally, I don't want to run the same query twice. It just feels dirty.
Let's take a quick look at the problem. Typical usage of the CI's pagination class looks something like this in the controller. I'll use a theoretical blog application for the code examples.
function index() {
// configure the paginator
$this->load->library('pagination');
$config['base_url'] = base_url() . 'blog/index/';
$config['per_page'] = 5;
$config['total_rows'] = $this->db->count_all('entries');
$this->pagination->initialize($config);
// load the model and get the query results
$this->load->model('Blog_model', 'blog');
$data['entries'] = $this->blog->select_entries($config['per_page'], $this->uri->segment(3));
// load the view
$this->load->view('entry_view', $data);
}
This works well enough when you don't have any WHERE conditions to apply. But if you do have WHERE conditions, you are going to have to execute two similar queries, one with a LIMIT clause, and one without. There is a more elegant solution. MySQL has functionality to calculate the number of rows in the full result set without running the query again. Let's apply this to the blog model's select_entries() function.
function select_entries($conditions = NULL, $limit = NULL, $offset = NULL) {
$this->db->select("SQL_CALC_FOUND_ROWS *");
if(isset($conditions)) {
$this->db->where($conditions, NULL, FALSE);
}
$query = $this->db->get('entries', $limit, $offset);
if($query->num_rows() > 0) {
// let's see how many rows would have been returned without the limit
$count_query = $this->db->query('SELECT FOUND_ROWS() AS row_count');
$found_rows = $count_query->row();
// load all of the returned results into a single array ($rows).
// this is handy if you need to execute other SQL statements or bring
// in additional model data that might be useful to have in this array.
// alternatively, you could return $query object if you prefer that.
$rows = array();
foreach($query->result() as $row) {
// to build on my comment above about returning an array instead of
// the raw $query object, as an example, this would be a good spot
// to retrieve the comment count for each entry and append that to
// the current row before we push the row data into the $rows array.
$row->comment_count = $this->_comment_count($row->entry_id);
array_push($rows, $row);
}
// after the foreach loop above, we should now have all of the combined
// entry data in a single array. let's return a two-element array: the
// first element contains the result set in array form, and the second
// element is the number of rows in the full result set without the limit
return array('rows' => $rows, 'found_rows' => (int) $found_rows->row_count);
} else {
return FALSE;
}
}
Now let's revise the controller.
function index() {
// configure the paginator
$this->load->library('pagination');
$config['base_url'] = base_url() . 'blog/index/';
$config['per_page'] = 5;
// load the model and get results
$this->load->model('Blog_model', 'blog');
$data['entries'] = $this->blog->select_entries("is_visible = 1", $config['per_page'], $this->uri->segment(3));
// set the total_rows value and initialize the paginator
$config['total_rows'] = (isset($data['entries']['found_rows'])) ? $data['entries']['found_rows'] : 0;
$this->pagination->initialize($config);
// load the view
$this->load->view('entry_view', $data);
}
Finally, iterate through the entries array in the view.
<div id="entries">
<?php foreach($entries['rows'] as $entry) { ?>
<div class="entry">
<h1 class="title"><?php echo $entry->title; ?></h1>
<div class="body"><?php echo $entry->body; ?></div>
</div>
<?php } ?>
</div>
</pre>
<?php echo $this->pagination->create_links(); ?>
That's it! Being somewhat new to CI, maybe there is a better way to do this? In fact, there probably is! I guess that is one of the things I like about CI so far... there is often more than one way to do things.
