Simple Pagination Using CodeIgniter and MySQL

TechArise Team

7 years ago

Pagination is one of the most frequently used features for web applications. So We have a share in this tutorial, how we can implement CodeIgniter pagination and enhance it further as per our need. Pagination is the process of dividing a document into discrete pages.

Also Read : Ajax Pagination Using CodeIgniter and MySQL

Before started to implement the Simple Pagination Using CodeIgniter and MySQL, look files structure:

pagination-using-codeigniter-mysql
- application
  - config
    - autoload.php
    - database.php
    - constants.php
    - pagination.php
    - routes.php
  - controllers
    - Employee.php
  - models
    - Employee_model.php
  - views
    - employee
      - index.php
    - templates
      - header.php
      - footer.php
- system
- index.php
- vendor
- assets
  - css

Step 1: Create the database and Table

For this tutorial, you need a MySQL database with the following table:

  //Table structure for table `employee`
  CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
  `name` varchar(255) NOT NULL COMMENT 'Employee Name',
  `email` varchar(255) NOT NULL COMMENT 'Email Address',
  `salary` float(10,2) NOT NULL COMMENT 'Employee Salary',
  `age` int(11) NOT NULL COMMENT 'Employee Age',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='datatable demo table' AUTO_INCREMENT=1;

//Dumping data for table `employee`

INSERT INTO `employee` (`id`, `name`, `email`, `salary`, `age`) VALUES
    (1, 'Nixon Tiger', 'tiger@techarise.com', 3208000.00, 45),
    (2, 'Garrett Winters', 'winters@techarise.com', 170750.00, 25),
    (3, 'Ashton Cox', 'cox@techarise.com', 86000.00, 66),
    (4, 'Cedric Kelly', 'kelly@techarise.com', 433060.00, 22),
    (5, 'Airi Satouy', 'airi@techarise.com', 162700.00, 33),
    (6, 'Brielle Williamson', 'will@techarise.com', 372000.00, 29),
    (7, 'Herrod Chandler', 'herrod@techarise.com', 137500.00, 39),
    (8, 'Rhona Davidson', 'rd@techarise.com', 327900.00, 36),
    (9, 'Colleen Hurst', 'colleen@techarise.com', 205500.00, 39),
    (10, 'Sonya Frost', 'frost@techarise.com', 103600.00, 23),
    (11, 'John Philip', 'filip@techarise.com', 26584.00, 25),
    (12, 'Sam Wood', 'sam@techarise.com', 26584.00, 27);

//Table structure for table `employee`

CREATE TABLE `employee` (

`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',

`name` varchar(255) NOT NULL COMMENT 'Employee Name',

`email` varchar(255) NOT NULL COMMENT 'Email Address',

`salary` float(10,2) NOT NULL COMMENT 'Employee Salary',

`age` int(11) NOT NULL COMMENT 'Employee Age',

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='datatable demo table' AUTO_INCREMENT=1;

//Dumping data for table `employee`

INSERT INTO `employee` (`id`, `name`, `email`, `salary`, `age`) VALUES

(1, 'Nixon Tiger', 'tiger@techarise.com', 3208000.00, 45),

(2, 'Garrett Winters', 'winters@techarise.com', 170750.00, 25),

(3, 'Ashton Cox', 'cox@techarise.com', 86000.00, 66),

(4, 'Cedric Kelly', 'kelly@techarise.com', 433060.00, 22),

(5, 'Airi Satouy', 'airi@techarise.com', 162700.00, 33),

(6, 'Brielle Williamson', 'will@techarise.com', 372000.00, 29),

(7, 'Herrod Chandler', 'herrod@techarise.com', 137500.00, 39),

(8, 'Rhona Davidson', 'rd@techarise.com', 327900.00, 36),

(9, 'Colleen Hurst', 'colleen@techarise.com', 205500.00, 39),

(10, 'Sonya Frost', 'frost@techarise.com', 103600.00, 23),

(11, 'John Philip', 'filip@techarise.com', 26584.00, 25),

(12, 'Sam Wood', 'sam@techarise.com', 26584.00, 27);

Step 2: Initialization CodeIgniter pagination library

Initialization CodeIgniter pagination library (file name: pagination.php) “application/config/” folder.

<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');
/**
 * Pagination Config
 * 
 * Just applying codeigniter's standard pagination config with twitter 
 * bootstrap stylings
 *
 * @author    Jaeeme Khan
 * @link    http://codeigniter.com/user_guide/libraries/pagination.html
 * @email   jaeeme.khan@sitanet.in
 * 
 * @file    pagination.php
 * @version   1.0.0.1
 * @date    08/09/2015
 * 
 * Copyright (c) 2015
 */
/* -------------------------------------------------------------------------- */

$config['per_page'] = 10;
$config['num_links'] = 2;

$config['use_page_numbers'] = TRUE;
$config['page_query_string'] = FALSE;

$config['query_string_segment'] = '';

$config['full_tag_open'] = '<ul class="pagination justify-content-end">';
$config['full_tag_close'] = '</ul>';
$config['attributes'] = ['class' => 'page-link'];

$config['first_link'] = '&laquo; First';
$config['first_tag_open'] = '<li class="page-item">';
$config['first_tag_close'] = '</li>';

$config['last_link'] = 'Last &raquo;';
$config['last_tag_open'] = '<li class="page-item">';
$config['last_tag_close'] = '</li>';

$config['next_link'] = 'Next &rarr;';
$config['next_tag_open'] = '<li class="page-item">';
$config['next_tag_close'] = '</li>';

$config['prev_link'] = '&larr; Prev';
$config['prev_tag_open'] = '<li class="page-item">';
$config['prev_tag_close'] = '</li>';

$config['cur_tag_open'] = '<li class="page-item active"><a href="#" class="page-link">';
$config['cur_tag_close'] = '<span class="sr-only">(current)</span></a></li>';
$config['num_tag_open'] = '<li class="page-item">';
$config['num_tag_close'] = '</li>';

$config['anchor_class'] = 'follow_link';
?>

<?php

if (!defined('BASEPATH'))

exit('No direct script access allowed');

/**

* Pagination Config

* Just applying codeigniter's standard pagination config with twitter

* bootstrap stylings

* @author Jaeeme Khan

* @link http://codeigniter.com/user_guide/libraries/pagination.html

* @email jaeeme.khan@sitanet.in

* @file pagination.php

* @version 1.0.0.1

* @date 08/09/2015

/* -------------------------------------------------------------------------- */

$config['per_page'] = 10;

$config['num_links'] = 2;

$config['use_page_numbers'] = TRUE;

$config['page_query_string'] = FALSE;

$config['query_string_segment'] = '';

$config['full_tag_open'] = '<ul class="pagination justify-content-end">';

$config['full_tag_close'] = '</ul>';

$config['attributes'] = ['class' => 'page-link'];

$config['first_link'] = '« First';

$config['first_tag_open'] = '<li class="page-item">';

$config['first_tag_close'] = '</li>';

$config['last_link'] = 'Last »';

$config['last_tag_open'] = '<li class="page-item">';

$config['last_tag_close'] = '</li>';

$config['next_link'] = 'Next →';

$config['next_tag_open'] = '<li class="page-item">';

$config['next_tag_close'] = '</li>';

$config['prev_link'] = '← Prev';

$config['prev_tag_open'] = '<li class="page-item">';

$config['prev_tag_close'] = '</li>';

$config['cur_tag_open'] = '<li class="page-item active"><a href="#" class="page-link">';

$config['cur_tag_close'] = '<span class="sr-only">(current)</span></a></li>';

$config['num_tag_open'] = '<li class="page-item">';

$config['num_tag_close'] = '</li>';

$config['anchor_class'] = 'follow_link';

per_page: Refers to the number of how many entries will be shown on each page.
base_url: Refers to the paginated url base.
total_rows: Total numer of entries in database.
use_page_numbers: Refers whether we want to use page number(1,2,3..) in the url or entry id(1,10, 20…) on uri segment.

Step 3: Create Model

Create a model file named Employee_model.php inside “application/models” folder.

<?php
defined('BASEPATH') OR 
exit('No direct script access allowed');
/**
 * Version: 1.0.0
 *
 * Description of Employee model
 *
 * @author TechArise Team
 *
 * @email  info@techarise.com
 *
 **/

// Employee class
class Employee_model extends CI_Model {
  // Declare variables
  private $_limit;
    private $_pageNumber;
    private $_offset;

  public function setLimit($limit) {
        $this->_limit = $limit;
    }

    public function setPageNumber($pageNumber) {
        $this->_pageNumber = $pageNumber;
    }

    public function setOffset($offset) {
        $this->_offset = $offset;
    }
  // Count all record of table "employee" in database.
  public function getAllEmployeeCount() {
        $this->db->from('employee');
        return $this->db->count_all_results();
    }
  // Fetch data according to per_page limit.
  public function employeeList() {       
        $this->db->select(array('e.id', 'e.name', 'e.email', 'e.salary', 'e.age'));
        $this->db->from('employee as e');          
        $this->db->limit($this->_pageNumber, $this->_offset);
        $query = $this->db->get();
        return $query->result_array();
    }

}
?>

<?php

defined('BASEPATH') OR

exit('No direct script access allowed');

/**

* Version: 1.0.0

* Description of Employee model

* @author TechArise Team

* @email info@techarise.com

**/

// Employee class

class Employee_model extends CI_Model {

// Declare variables

private $_limit;

private $_pageNumber;

private $_offset;

public function setLimit($limit) {

$this->_limit = $limit;

}

public function setPageNumber($pageNumber) {

$this->_pageNumber = $pageNumber;

}

public function setOffset($offset) {

$this->_offset = $offset;

}

// Count all record of table "employee" in database.

public function getAllEmployeeCount() {

$this->db->from('employee');

return $this->db->count_all_results();

}

// Fetch data according to per_page limit.

public function employeeList() {

$this->db->select(array('e.id', 'e.name', 'e.email', 'e.salary', 'e.age'));

$this->db->from('employee as e');

$this->db->limit($this->_pageNumber, $this->_offset);

$query = $this->db->get();

return $query->result_array();

}

Load “pagination” class in controller.

  <?php 
  // load library
  $this->load->library("pagination"); 
  ?>

<?php

// load library

$this->load->library("pagination");

Step 4: Create controllers

Create a controllers file named Employee.php inside “application/controllers” folder.

The Employee controller handles the calendar process.
__construct() – Loads the required library, helper and model.
index() – load index employee list.

<?php
defined('BASEPATH') OR 
exit('No direct script access allowed');
/**
 * Version: 1.0.0
 *
 * Description of Employee Controller
 *
 * @author TechArise Team
 *
 * @email  info@techarise.com
 *
 **/

// Employee class
class Employee extends CI_Controller {
  //Load libraries in Constructor.
  public function __construct() {
        parent::__construct();        
        $this->load->library('pagination');
        $this->load->model('Employee_model', 'employee');

    }
  // index method
  public function index() {
        $data['metaDescription'] = 'Simple Pagination Using CodeIgniter and MySQL';
        $data['metaKeywords'] = 'Simple Pagination Using CodeIgniter and MySQL';
        $data['title'] = "Simple Pagination Using CodeIgniter and MySQL - TechArise";
        $data['breadcrumbs'] = array('Simple Pagination Using CodeIgniter and MySQL' => '#'); 
        $config['total_rows'] = $this->employee->getAllEmployeeCount();
        $data['total_count'] = $config['total_rows'];
        $config['suffix'] = '';
        if ($config['total_rows'] > 0) {
            $page_number = $this->uri->segment(3);
            if ($page_number > 0) {
                $config['base_url'] = base_url() . 'employee';
            } else {
                $config['base_url'] = base_url() . 'employee/index/';
            }
            if (empty($page_number))
                $page_number = 1;
            $offset = ($page_number - 1) * $this->pagination->per_page;
            $this->employee->setPageNumber($this->pagination->per_page);
            $this->employee->setOffset($offset);
            $this->pagination->cur_page = $offset;
            $config['attributes'] = array('class' => 'page-link');
            $this->pagination->initialize($config);
            $data['page_links'] = $this->pagination->create_links();
            $data['employeeInfo'] = $this->employee->employeeList();
        }
        $this->load->view('employee/index', $data);
    }
}
?>

<?php

defined('BASEPATH') OR

exit('No direct script access allowed');

/**

* Version: 1.0.0

* Description of Employee Controller

* @author TechArise Team

* @email info@techarise.com

**/

// Employee class

class Employee extends CI_Controller {

//Load libraries in Constructor.

public function __construct() {

parent::__construct();

$this->load->library('pagination');

$this->load->model('Employee_model', 'employee');

}

// index method

public function index() {

$data['metaDescription'] = 'Simple Pagination Using CodeIgniter and MySQL';

$data['metaKeywords'] = 'Simple Pagination Using CodeIgniter and MySQL';

$data['title'] = "Simple Pagination Using CodeIgniter and MySQL - TechArise";

$data['breadcrumbs'] = array('Simple Pagination Using CodeIgniter and MySQL' => '#');

$config['total_rows'] = $this->employee->getAllEmployeeCount();

$data['total_count'] = $config['total_rows'];

$config['suffix'] = '';

if ($config['total_rows'] > 0) {

$page_number = $this->uri->segment(3);

if ($page_number > 0) {

$config['base_url'] = base_url() . 'employee';

} else {

$config['base_url'] = base_url() . 'employee/index/';

}

if (empty($page_number))

$page_number = 1;

$offset = ($page_number - 1) * $this->pagination->per_page;

$this->employee->setPageNumber($this->pagination->per_page);

$this->employee->setOffset($offset);

$this->pagination->cur_page = $offset;

$config['attributes'] = array('class' => 'page-link');

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

$data['page_links'] = $this->pagination->create_links();

$data['employeeInfo'] = $this->employee->employeeList();

}

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

}

Step 5:Create a view(header)

Create a view file named header.php inside “application/views/templates” folder.

This view contains the header section of the webpage. The Bootstrap library is used to provide a better UI, so, include it in the header section.

  <?php
defined('BASEPATH') OR exit('No direct script access allowed');
?>
<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
  <meta name="description" content="">
  <meta name="author" content="">
  <title><?php print $title; ?></title>
  <link rel="icon" type="image/ico" href="<?php print HTTP_IMAGE_PATH; ?>favicon.ico">
  <!-- Bootstrap core CSS -->
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.3.1/css/bootstrap.min.css" />
  <!-- Custom fonts for this template -->
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/5.7.2/css/all.min.css" />
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/simple-line-icons/2.4.1/css/simple-line-icons.css" />
  <link href="https://fonts.googleapis.com/css?family=Lato:300,400,700,300italic,400italic,700italic" rel="stylesheet" type="text/css">
  <!-- Custom styles for this template -->
  <link href="<?php print HTTP_CSS_PATH; ?>style.css" rel="stylesheet">
</head>
<body>
  <!-- Navigation -->
  <nav class="navbar navbar-expand-lg navbar-dark bg-dark static-top header-bg-dark" style="background: ##FFFFFF!;">
  <div class="container">
    <a class="navbar-brand font-weight-bold" href="https://techarise.com"><h1>Tech Arise</h1></a>
    <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarResponsive" aria-controls="navbarResponsive" aria-expanded="false" aria-label="Toggle navigation">
          <span class="navbar-toggler-icon"></span>
        </button>
    <div class="collapse navbar-collapse" id="navbarResponsive">
      <ul class="navbar-nav ml-auto">
        <li class="nav-item active">
          <a class="nav-link" href="https://techarise.com">Home
                <span class="sr-only">(current)</span>
              </a>
        </li>
        <li class="nav-item">
          <a class="nav-link" href="https://techarise.com/php-free-script-demos/">Live Demo</a>
        </li>
      </ul>
    </div>
  </div>
</nav>

<?php

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

<!DOCTYPE html>

<head>

</head>

<body>

<a class="navbar-brand font-weight-bold" href="https://techarise.com"><h1>Tech Arise</h1></a>

</button>

<a class="nav-link" href="https://techarise.com">Home

<span class="sr-only">(current)</span>

</a>

</li>

</li>

</ul>

</div>

</nav>

Step 6: Create a view(footer)

Create a view file named footer.php inside “application/views/templates” folder.

This view contains the footer section of the webpage.

<!-- Footer -->
  <footer class="footer bg-light footer-bg-dark">
    <div class="container">
      <div class="row">
        <div class="col-lg-6 h-100 text-center text-lg-left my-auto">
          <ul class="list-inline mb-2">
            <li class="list-inline-item">
              <a href="#">About</a>
            </li>
            <li class="list-inline-item">&sdot;</li>
            <li class="list-inline-item">
              <a href="#">Contact</a>
            </li>
            <li class="list-inline-item">&sdot;</li>
            <li class="list-inline-item">
              <a href="#">Terms of Use</a>
            </li>
            <li class="list-inline-item">&sdot;</li>
            <li class="list-inline-item">
              <a href="#">Privacy Policy</a>
            </li>
          </ul>
          <p class="text-muted small mb-4 mb-lg-0">Copyright &copy;  2011 - <?php print date('Y', time());?> <a href="https://techarise.com/">TECHARISE.COM</a> All rights reserved.</p>
        </div>
        <div class="col-lg-6 h-100 text-center text-lg-right my-auto">
          <ul class="list-inline mb-0">
            <li class="list-inline-item mr-3">
              <a href="#">
                <i class="fab fa-facebook fa-2x fa-fw"></i>
              </a>
            </li>
            <li class="list-inline-item mr-3">
              <a href="#">
                <i class="fab fa-twitter-square fa-2x fa-fw"></i>
              </a>
            </li>
            <li class="list-inline-item">
              <a href="#">
                <i class="fab fa-instagram fa-2x fa-fw"></i>
              </a>
            </li>
          </ul>
        </div>
      </div>
    </div>
  </footer>
  <script>
    var baseurl = "<?php print site_url();?>";
  </script>
  <!-- Bootstrap core JavaScript -->
  <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.3.1/js/bootstrap.bundle.min.js"></script>  
</body>
</html>

<a href="#">About</a>

</li>

<a href="#">Contact</a>

</li>

<a href="#">Terms of Use</a>

</li>

<a href="#">Privacy Policy</a>

</li>

</ul>

</div>

</a>

</li>

</a>

</li>

</a>

</li>

</ul>

</div>

</footer>

var baseurl = "<?php print site_url();?>";

</script>

</body>

</html>

Step 7: Create views

Create a views file named index.php inside “application/views/employee” folder.

 <?php $this->load->view('templates/header');?>
  <section class="showcase">
    <div class="container">
      <div class="pb-2 mt-4 mb-2 border-bottom">
        <h2>Simple Pagination Using CodeIgniter and MySQL</h2>
      </div>
      <div class="row">
          <div class="col-lg-12 col-md-12 mb-12">
            <table class="table table-bordered">
              <thead>
                <tr>                 
                  <th scope="col">Name</th>
                  <th scope="col">Email</th>
                  <th scope="col">Salary</th>
                  <th scope="col">Age</th>
                </tr>
              </thead>
              <tbody>
                <?php if (isset($employeeInfo) && !empty($employeeInfo)) { ?>
                  <?php foreach ($employeeInfo as $key => $element) { ?>
                  <tr>                    
                    <td><?php echo $element['name']; ?></td>   
                    <td><?php echo $element['email']; ?></td> 
                    <td><?php echo $element['salary']; ?></td>                       
                    <td><?php echo $element['age']; ?></td>
                  </tr>
                  <?php } ?>
                 <?php } else { ?>
                  <tr>
                    <td colspan="4">There is no employee.</td>  
                  </tr>
                 <?php } ?>
              </tbody>
            </table>
          </div>      
      </div>
    <div class="row">
          <div class="col-lg-12 text-right">
            <?php if (isset($employeeInfo) && is_array($employeeInfo)) echo $page_links; ?>
          </div>
        </div>
    </div>
  </section>
<?php $this->load->view('templates/footer');?>

<?php $this->load->view('templates/header');?>

<h2>Simple Pagination Using CodeIgniter and MySQL</h2>

</div>

<thead>

<tr>

<th scope="col">Email</th>

<th scope="col">Salary</th>

</tr>

</thead>

<tbody>

<?php if (isset($employeeInfo) && !empty($employeeInfo)) { ?>

<?php foreach ($employeeInfo as $key => $element) { ?>

<tr>

</tr>

<?php } ?>

<?php } else { ?>

<tr>

<td colspan="4">There is no employee.</td>

</tr>

<?php } ?>

</tbody>

</table>

</div>

<?php if (isset($employeeInfo) && is_array($employeeInfo)) echo $page_links; ?>

</div>

</section>

<?php $this->load->view('templates/footer');?>

Demo Download