Databases are living entities. They grow as time passes by. When designing applications, we need to take into consideration the many records that the users will have to browse through. Pagination allows us to break down our results into small manageable parts. The following image shows an example of pagination on Google search.

In this tutorial, you are going to learn how to paginate database results in CodeIgniter using the pagination library. This tutorial presumes that you are familiar with the basics of CodeIgniter Active Record. If you don't, then you can read the previous tutorials. The other assumption made is that you have already downloaded CodeIgniter.

In this tutorial, you will learn-

Database configuration

We will start with creating the database and inserting some dummy records in it. It will be a single table database with 50 records on it.

Run the following script against MySQL to create the database authors table. Insert 50 dummy records.

CREATE SCHEMA ci_pagination;

DROP TABLE IF EXISTS `authors`;

CREATE TABLE `authors` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `last_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `birthdate` date NOT NULL,
  `added` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (1, 'Brielle', 'O\'Hara', This email address is being protected from spambots. You need JavaScript enabled to view it.
	', '1974-05-21', '1999-09-17 19:17:28');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (2, 'Flavio', 'Lehner', This email address is being protected from spambots. You need JavaScript enabled to view it.', '1971-10-14', '1998-09-09 00:25:06');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (3, 'Elmira', 'Johns', This email address is being protected from spambots. You need JavaScript enabled to view it.', '1983-11-28', '2015-02-07 15:56:43');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (4, 'Elza', 'Mitchell', This email address is being protected from spambots. You need JavaScript enabled to view it.', '1989-03-08', '1992-08-21 00:21:39');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (5, 'Viva', 'Greenfelder', This email address is being protected from spambots. You need JavaScript enabled to view it.', '1995-04-17', '2017-04-30 05:55:39');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (6, 'Maxwell', 'VonRueden', This email address is being protected from spambots. You need JavaScript enabled to view it.', '1994-07-01', '1996-05-08 23:30:14');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (7, 'Deontae', 'Becker', This email address is being protected from spambots. You need JavaScript enabled to view it.', '1992-02-19', '2017-07-22 11:49:15');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (8, 'Sylvester', 'Christiansen', This email address is being protected from spambots. You need JavaScript enabled to view it.', '1990-09-03', '2004-05-08 08:15:37');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (9, 'Torrey', 'Strosin', This email address is being protected from spambots. You need JavaScript enabled to view it.', '1999-10-09', '2009-08-30 21:30:44');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (10, 'Kelli', 'Smitham', This email address is being protected from spambots. You need JavaScript enabled to view it.', '2012-11-07', '1986-01-22 20:52:57');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (11, 'Abigale', 'Schuppe', This email address is being protected from spambots. You need JavaScript enabled to view it.', '2018-02-18', '1994-05-07 06:26:36');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (12, 'Letha', 'Gleason', This email address is being protected from spambots. You need JavaScript enabled to view it.', '2009-02-06', '1998-05-25 04:37:54');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (13, 'Sienna', 'Upton', This email address is being protected from spambots. You need JavaScript enabled to view it.', '2017-08-17', '2009-08-08 19:08:10');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (14, 'Harley', 'Gutkowski', This email address is being protected from spambots. You need JavaScript enabled to view it.', '1987-11-30', '1998-07-31 11:08:01');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (15, 'Lois', 'Bednar', This email address is being protected from spambots. You need JavaScript enabled to view it.', '2002-02-11', '2001-07-26 15:04:16');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (16, 'Gail', 'McDermott', This email address is being protected from spambots. You need JavaScript enabled to view it.', '1987-03-30', '2004-12-15 20:38:29');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (17, 'Sydney', 'Strosin', This email address is being protected from spambots. You need JavaScript enabled to view it.', '1985-04-29', '2010-08-07 08:50:35');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (18, 'Anne', 'Cremin', This email address is being protected from spambots. You need JavaScript enabled to view it.', '1976-02-15', '1980-03-25 05:29:41');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (19, 'Norberto', 'Bergnaum', This email address is being protected from spambots. You need JavaScript enabled to view it.', '1971-03-11', '1993-03-20 23:36:25');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (20, 'Arvid', 'Johns', This email address is being protected from spambots. You need JavaScript enabled to view it.', '2016-01-03', '1995-04-30 17:07:15');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (21, 'Bernita', 'Fay', This email address is being protected from spambots. You need JavaScript enabled to view it.', '1983-12-26', '1987-02-23 16:55:28');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (22, 'Gonzalo', 'Gorczany', This email address is being protected from spambots. You need JavaScript enabled to view it.', '1987-10-19', '2016-10-18 19:25:46');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (23, 'Jackie', 'Klein', This email address is being protected from spambots. You need JavaScript enabled to view it.', '1971-01-20', '2010-11-16 02:58:05');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (24, 'Andrew', 'Kessler', This email address is being protected from spambots. You need JavaScript enabled to view it.', '1984-08-07', '1971-12-15 08:46:42');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (25, 'Claire', 'Hudson', This email address is being protected from spambots. You need JavaScript enabled to view it.', '1984-12-19', '1991-07-04 14:35:53');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (26, 'Prudence', 'Runte', This email address is being protected from spambots. You need JavaScript enabled to view it.', '2008-12-27', '1985-09-30 04:57:55');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (27, 'Destinee', 'Romaguera', This email address is being protected from spambots. You need JavaScript enabled to view it.', '1978-11-09', '1997-11-09 19:58:08');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (28, 'Marianna', 'Harvey', This email address is being protected from spambots. You need JavaScript enabled to view it.', '2013-08-28', '1990-08-06 19:29:19');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (29, 'Eloy', 'Lakin', This email address is being protected from spambots. You need JavaScript enabled to view it.', '1989-12-08', '1974-03-16 03:21:16');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (30, 'Rubie', 'McClure', This email address is being protected from spambots. You need JavaScript enabled to view it.', '1987-09-12', '1998-07-02 10:45:36');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (31, 'Marisa', 'Stracke', This email address is being protected from spambots. You need JavaScript enabled to view it.', '1975-05-28', '1975-03-19 00:57:35');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (32, 'Jacey', 'Beatty', This email address is being protected from spambots. You need JavaScript enabled to view it.', '1979-12-17', '1971-01-21 16:50:58');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (33, 'Idella', 'Ruecker', This email address is being protected from spambots. You need JavaScript enabled to view it.', '2009-09-15', '1996-04-19 22:27:31');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (34, 'Dimitri', 'Bednar', This email address is being protected from spambots. You need JavaScript enabled to view it.', '1998-12-02', '2008-12-30 23:29:57');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (35, 'Elfrieda', 'Smitham', This email address is being protected from spambots. You need JavaScript enabled to view it.', '2018-07-23', '1972-04-01 07:52:25');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (36, 'Dwight', 'Walter', This email address is being protected from spambots. You need JavaScript enabled to view it.', '2016-08-15', '1994-07-02 11:06:55');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (37, 'Macie', 'Fay', This email address is being protected from spambots. You need JavaScript enabled to view it.', '1976-01-29', '2005-12-13 18:44:46');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (38, 'Lori', 'Kshlerin', This email address is being protected from spambots. You need JavaScript enabled to view it.', '1983-04-08', '1986-01-18 16:03:52');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (39, 'Jeffry', 'Paucek', This email address is being protected from spambots. You need JavaScript enabled to view it.', '2002-10-02', '2004-03-29 07:06:03');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (40, 'Yvonne', 'Bernhard', This email address is being protected from spambots. You need JavaScript enabled to view it.', '2017-11-04', '1986-12-15 23:55:23');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (41, 'Ludwig', 'Heathcote', This email address is being protected from spambots. You need JavaScript enabled to view it.', '1981-02-09', '2000-08-02 20:45:48');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (42, 'Jadyn', 'Wiegand', This email address is being protected from spambots. You need JavaScript enabled to view it.', '1982-06-16', '1980-12-05 13:09:37');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (43, 'Ferne', 'Smitham', This email address is being protected from spambots. You need JavaScript enabled to view it.', '1996-12-17', '1984-11-18 19:26:27');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (44, 'Meta', 'Corkery', This email address is being protected from spambots. You need JavaScript enabled to view it.', '1972-03-31', '2007-01-20 00:07:31');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (45, 'Toni', 'Wolf', This email address is being protected from spambots. You need JavaScript enabled to view it.', '2012-04-15', '1973-06-22 12:14:37');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (46, 'Zack', 'Luettgen', This email address is being protected from spambots. You need JavaScript enabled to view it.', '2003-05-29', '2018-11-19 05:03:21');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (47, 'Kameron', 'Dietrich', This email address is being protected from spambots. You need JavaScript enabled to view it.', '1973-05-20', '1974-09-16 20:27:17');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (48, 'Zetta', 'Lebsack', This email address is being protected from spambots. You need JavaScript enabled to view it.', '1996-11-04', '2001-03-06 01:33:01');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (49, 'Benny', 'Hane', This email address is being protected from spambots. You need JavaScript enabled to view it.', '2008-02-02', '2006-01-27 22:12:39');
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES (50, 'Evans', 'Gleason', This email address is being protected from spambots. You need JavaScript enabled to view it.', '1999-09-09', '1990-09-26 17:50:11');

Now that we have created our database successfully and inserted dummy records in it, let's configure our CodeIgniter application to communicate with the database.

Open application/config/database.php

Set the database connection parameters similar to the following

$db['default'] = array(
	'dsn'	=> '',
	'hostname' => 'localhost',
	'username' => 'root',
	'password' => 'melody',
	'database' => 'ci_pagination',
	'dbdriver' => 'mysqli',
	'dbprefix' => '',
	'pconnect' => FALSE,
	'db_debug' => (ENVIRONMENT !== 'production'),
	'cache_on' => FALSE,
	'cachedir' => '',
	'char_set' => 'utf8',
	'dbcollat' => 'utf8_general_ci',
	'swap_pre' => '',
	'encrypt' => FALSE,
	'compress' => FALSE,
	'stricton' => FALSE,
	'failover' => array(),
	'save_queries' => TRUE
);

HERE,

  • 'hostname' => 'localhost', sets the host name of the database
  • 'username' => 'root', specifies the database name
  • 'password' => 'openseseme', sets the database password
  • 'database' => 'ci_pagination', specifies the database name.

We will also be autoloading the database library when our application starts. Let's do that. Open application/config/autoload.php

Load the database library as shown by the code below

$autoload['libraries'] = array('database');

That's it for the database configuration.

Let's now work on the database model for pagination

CodeIgniter Pagination Database Model

Create a new model Authors_model in application/models

Add the following code

<?php

class Authors_model extends CI_Model {

    protected $table = 'authors';

    public function __construct() {
        parent::__construct();
    }

    public function get_count() {
        return $this->db->count_all($this->table);
    }

    public function get_authors($limit, $start) {
        $this->db->limit($limit, $start);
        $query = $this->db->get($this->table);

        return $query->result();
    }
}

HERE,

  • protected $table = 'authors'; define a protected variable that defines the database table name for the model
  • public function __construct() {…} calls the parent constructor method
  • public function get_count() {…} returns the total records in the database table. This is a need for pagination purposes
  • public function get_authors($limit, $start) {…} defines a method that will be used to retrieve paginated results from the database. We will be passing in the limit and the start point. The limit defines the total number of records to be returned while start defines the number of records that should be skipped

that's it for our database model. Let's now create the routes that will be responding to our paginated results.

CodeIgniter Pagination Routes

Open the routes file in application/config/routes.php

Add the following route

$route['authors/(:num)'] = 'authors';

HERE,

  • We define a route author that accepts an optional parameter of the number. The optional parameter is defined by using the opening and closing brackets. The defined route calls the index method in the Authors controller.

Let's now move on to the controller for our paginated results

CodeIgniter Pagination Controller

Create a new file Authors.php in application/controllers directory

Add the following code

<?php defined('BASEPATH') OR exit('No direct script access allowed');

class Authors extends CI_Controller {

    public function __construct() {
        parent:: __construct();

        $this->load->helper('url');
        $this->load->model('authors_model');
        $this->load->library("pagination");
    }

    public function index() {
        $config = array();
        $config["base_url"] = base_url() . "authors";
        $config["total_rows"] = $this->authors_model->get_count();
        $config["per_page"] = 10;
        $config["uri_segment"] = 2;

        $this->pagination->initialize($config);

        $page = ($this->uri->segment(2)) ? $this->uri->segment(2) : 0;

        $data["links"] = $this->pagination->create_links();

        $data['authors'] = $this->authors_model->get_authors($config["per_page"], $page);

        $this->load->view('authors/index', $data);
    }
}

HERE,

  • class Authors extends CI_Controller {…} we define a class Authors that extends the CI_Controller class
  • public function __construct() {…} this method initializes the parent constructor and loads the url helper, authors model and the pagination library.
  • public function index() {…} defines the method that responds to our route authors
    • $config["base_url"] = base_url() . "authors"; sets the pagination URL that will be used to generate pagination links
    • $config["total_rows"] = $this->authors_model->get_count(); sets the total row that need to be paginated. The value is retrieved from the authors model by calling the get_count method.
    • $config["per_page"] = 10; defines the rows that need to be displayed per page
    • $config["uri_segment"] = 2; specifies the URL segment that contains the value that will be used to skip records
    • $this->pagination->initialize($config); initializes the pagination library using the config array value that we defined and assign the above values to.
    • $page = ($this->uri->segment(2)) ? $this->uri->segment(2) : 0; checks if the skip number has been set in the second segment of the URI and if its not set then the value 0 is assigned to the variable $page
    • $data["links"] = $this->pagination->create_links(); creates the pagination links and assigns them to the links key of the $data array variable.
    • data['authors'] = $this->authors_model->get_authors($config["per_page"], $page); retrieved the paginated authors records and assigns them to the authors key of the $data array variable
    • $this->load->view('authors/index', $data); loads the index view in the authors directory and passes in the array variable $data.

That's it for our model. Let's now create the view that will display our database results.

Create a new directory authors in application/views

Create a new file index.php in application/views/authors/index.php

Add the following code

<!DOCTYPE html>
<html>
    <head>
        <title>CodeIgniter Pagination</title>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/bulma/0.7.2/css/bulma.min.css">
    </head>
    <body>
        <div class="container">
            <h3 class="title is-3">CodeIgniter Database Pagination</h3>
            <div class="column">
                <table class="table is-bordered is-striped is-narrow is-hoverable is-fullwidth">
                    <thead>
                        <tr>
                            <th>ID</th>
                            <th>Contact Name</th>
                            <th>Contact Number</th>
                            <th>Email</th>
                            <th>City</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($authors as $author): ?>
                            <tr>
                                <td><?= $author->id ?></td>
                                <td><?= $author->first_name ?></td>
                                <td><?= $author->last_name ?></td>
                                <td><?= $author->email ?></td>
                                <td><?= $author->birthdate ?></td>
                            </tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
                <p><?php echo $links; ?></p>
            </div>
        </div>
    </body>
</html>

HERE,

  • <?php foreach ($authors as $author): ?> loops through the results of the $authors variable and prints the results in table
  • <p><?php echo $links; ?></p> prints the pagination links at the bottom of our table.

Let's now start our application and see the results

In this tutorial, we are using the built-in PHP web server, but you can use any web server that supports PHP.

Open the terminal

Run the following command

cd C:\Sites\ci-app
php -S localhost:3000

HERE,

  • The above command browses to the application code directory and starts the built-in server on port 3000.

Note: the application path has to match the path where you downloaded CodeIgniter. For that, you can use any port number that is free on your computer. It's not necessary to use port 3000.

Open the web browser and load the following URL

http://localhost:3000/authors

You should be able to get results similar to the following

Summary

In this tutorial, we have covered the basics of pagination and took advantage of the pagination library in CodeIgniter and used it to create an application that paginates database results.

 

YOU MIGHT LIKE: