Paginating Database Records with the Code Igniter PHP Framework

In the previous tutorials in this nine-part series, we built a simple application with the Code Igniter PHP framework. In this article, the third part of the series, we’re going to add some important functionality to this application by using the pager class included with Code Igniter.


Building PHP applications using a third-party framework can be a time-consuming process, particularly in those cases where the software chosen requires installing and configuring a large number of source files, as well as dealing with a long and difficult learning curve.

Luckily, not all PHP frameworks are created in the same painful way. A good example of this is Code Igniter, a solid piece of software written in PHP 4 that allows you to develop full-featured web applications by means of a friendly API, and by taking advantage of the functionality provided by the Model-View-Controller pattern.

Therefore, if you’re interested in learning how to use the powerful tools that come packaged with Code Igniter and start creating full-blown database-driven applications in a very short time, then you should start reading this group of articles now!

And speaking of articles, you’ll surely recall that in the previous one, I explained how to develop a simple MySQL-based web application, which was capable of fetching a few rows straight from a database table and displaying their contents on the browser.

However, even though the functionality of this introductory application was fairly limited, it’s worth mentioning that this example was built by utilizing the MVC pattern. This means that it was comprised of three modules, that is a model class, then the corresponding controller and finally a simple view for embedding these database rows into a basic HTML page.

As you may guess, however, Code Igniter comes bundled with a robust set of core classes, which can be used to perform all sorts of clever tasks, such as working with databases, performing file uploads, validating user-supplied data, and so forth.

So, in this third chapter of this series, I’m going to show you how to improve the MySQL-driven application developed in the preceding tutorial, since it’ll be provided with the capacity for paging database records, via the pager class included with Code Igniter.

Now, it’s time to get rid of the boring theory and see how easy is to build a pagination system with Code Igniter. Let’s jump in!

{mospagebreak title=Retrieving user-related data from a MySQL table}

Since my plan consists basically of demonstrating how to build a basic PHP application, capable of paging a few database rows, I’m going to use the same sample “users” MySQL table that was created in the previous article. As you’ll recall, the table was populated with data about some fictional users. Its structure looked like this:

Now that you’re familiar with the above MySQL table, it’s time to build a model class that permits us to access its rows in a painless fashion. So take a look at the following “User” model class, which accomplishes this task. Here it is:

class Users extends Model{

function Users(){

// call the Model constructor


// load database class and connect to MySQL



function getAllUsers(){



// return result set as an associative array

return $query->result_array();



function getUsersWhere($field,$param){



// return result set as an associative array

return $query->result_array();


// get 5 rows at a time

function getUsers($row){



// return result set as an associative array

return $query->result_array();



// get total number of users

function getNumUsers(){

return $this->db->count_all(‘users’);



If you read the preceding tutorial of the series, it’s possible that you find the above model class pretty familiar. As you can see, it presents a group of methods that can be used for fetching both single and multiple rows from the pertinent “users” MySQL table, as well for counting the number of records contained within a given result set.

Also, it’s worthwhile to clarify again that this model implements all of its methods thanks to the functionality of the database class included with Code Igniter, which is loaded by the corresponding constructor.

However, you should pay close attention to the signature of a brand new method, called “getUsers().” It permits the program to fetch five rows at time.  I’m going to take advantage of its functionality to build a simple, yet effective, paging system.

Now that you hopefully grasped how the previous model class does its thing, please save it to the system/application/models/ folder and jump forward to see how to define the other building block of this MySQL-driven application. In this case, I’m talking about the corresponding controller class, which not only will be capable of embedding database contents into a view file, but will also implement the aforementioned paging mechanism.

To learn how this controller class will be constructed, please click on the link that appears below and keep reading.

{mospagebreak title=Paging database records with Code Igniter}

Indeed, the philosophy that drives Code Igniter allows us to simplify the development of complex applications. And paginating database records certainly isn’t an exception, since the framework includes a solid pager class, which can be easily customized to suit the requirement of a huge number of projects.

In this particular case, where it’s necessary to create a user controller class that uses the methods of the model coded previously, the pager class of Code Igniter fits perfectly into this schema. Below I built a brand new controller that exploits the functionality of this pager class to paginate rows fetched from the prior “users” MySQL table.

Here’s the code for this class:

class Users extends Controller{

function Users(){

// load controller parent


// load ‘Users’ model



function display($row=0){

// load pagination library


// set pagination parameters





// store data for being displayed on view file


$data['title']=’Displaying user data’;

$data['header']=’User List’;


// load ‘testview’ view




As illustrated above, the “User” controller class looks pretty similar to the one built in the previous tutorial. However, there’s an important difference that I’d like to point out here, since it implements a new method called “display().”

As you can see, the method in question first includes, via the corresponding loader object, the pagination class. Then, it initializes some of its parameters, such as the base URL of the paging links, the total number of rows that will be spawned, and finally the number of records that will be displayed per page.

Once the pagination class has been correctly set up, it’s used for creating the paging links via its “create_links()” method. Lastly, the “display()” method retrieves the paginated rows from the corresponding “users” MySQL table, which are directly embedded along with the pertinent links into a view file. And before I forget, please save the user controller class to the Code Igniter system/application/controllers/ folder.

However, if you’re anything like me, you wonder how the view file will be created, right? Thus, in the last section of this tutorial I’m going to show you how to define this file, in this way finishing the development of this MySQL-driven application aimed at demonstrating how to paginate database rows with Code Igniter.

What are you waiting for? Jump forward and read the next section!

{mospagebreak title=Completing the development of the sample MySQL-driven application}

In the section that you just read, you learned how to use the pagination class included with Code Igniter to display a few paginated rows, which were retrieved from the corresponding “users” MySQL table, defined earlier.

In this case, though, the term “display” isn’t totally correct, since it’s necessary to build a view file first, which will actually print this data on the browser. Taking into account this concept, below I listed the signature of the file in question, so you can grasp quickly how it works. Take a look at it, please:



<title><?php echo $title;?></title>



<h1><?php echo $header;?></h1>


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


<p><?php echo ‘Full Name: ‘.$user['firstname'].’ ‘.$user['lastname'].’ Email: ‘.$user['email'];?></p>


<?php endforeach;?>


<p><?php echo $links;?></p>



If you examine in detail the above view file, you’ll have to agree with me that it looks really simple! As show before, the file includes a few echo statements, which are used to display the paged rows and for showing the respective page links.

Asides from listing for you the prior view file, I also included below a couple of illustrative images, which show how the paginated records are displayed on the browser. Here they are:

See how simple it is to paginate database rows with Code Igniter? I bet you do! Indeed, the whole process is reduced to defining a controller that uses its pagination class, and then creating a view file that displays the paged rows. That’s all.

And now that I mentioned a view file, don’t forget to save the previous one to the Code Igniter /system/application/views/ folder, so you can test the complete database-driven application by typing the following URL:

With this final example, I’m wrapping up this tutorial on paginating database records. As usual with many of my articles on PHP development, feel free to tweak all of the code samples shown in this article, so you can acquire a more solid background in using Code Igniter.

Final thoughts

In this third chapter of the series, I demonstrated how easy it is to develop a MySQL-based web application that implements an effective paging mechanism with the Code Igniter PHP framework. Undoubtedly, this particular example demonstrates that a framework can be friendly and powerful at the same time.

In the next installment, I’ll be showing you how to use Code Igniter’s validation class along with some helper functions to validate web forms.

Don’t miss the next article!

Google+ Comments

Google+ Comments