Pagination Using CodeIgniter and MySQL

Pagination, also known as paging, is the process of dividing a document into discrete pages.Pagination allows us to break down our results into small manageable parts. In this tutorial I am going to explain how to create pagination or feature using Pagination Using CodeIgniter and MySQL. This is a very simple example, you can just copy paste and change according to your requirement.

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

pagination-using-codeIgniter-mysql
- application
  - config
    - autoload.php
    - database.php
    - constants.php
    - pagination.php
    - routes.php
  - models
    - News_model.php
  - controllers
    - News.php
  - views
    - news
      - index.php
    - templates
      - header.php
      - footer.php
- system
- index.php
- assets
  - css

Step 1: Create the database and Table

Run the following script against MySQL to create the database news table.

  -- Table structure for table `news`

  CREATE TABLE `news` (
  `id` int(11) NOT NULL,
  `title` varchar(255) DEFAULT 'NULL',
  `description` text NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `user_pic` varchar(255) NOT NULL,
  `created_date` varchar(12) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping data for table `news`


-- Indexes for table `news`

ALTER TABLE `news`
  ADD PRIMARY KEY (`id`);

-- AUTO_INCREMENT for table `news`

ALTER TABLE `news`
    MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=18;

-- Table structure for table `news`

CREATE TABLE `news` (

`id` int(11) NOT NULL,

`title` varchar(255) DEFAULT 'NULL',

`description` text NOT NULL,

`name` varchar(255) NOT NULL,

`email` varchar(255) NOT NULL,

`user_pic` varchar(255) NOT NULL,

`created_date` varchar(12) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping data for table `news`

-- Indexes for table `news`

ALTER TABLE `news`

ADD PRIMARY KEY (`id`);

-- AUTO_INCREMENT for table `news`

ALTER TABLE `news`

MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=18;

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    Coders Mag Team
 * @link    http://codeigniter.com/user_guide/libraries/pagination.html
 * @email   infon@codersmag.com
 * 
 * @file    pagination.php
 * @version   1.0.0.1
 * @date    28/09/2019
 * 
 */
/* -------------------------------------------------------------------------- */

$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 Coders Mag Team

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

* @email infon@codersmag.com

* @file pagination.php

* @version 1.0.0.1

* @date 28/09/2019

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

$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';

Step 3: Create Model

Need a model file News_model.php inside “application/models” folder, that fetches records from the news table

<?php
/**
 * Description of News Model pagination
 *
 * @author CodersMag Team
 */
defined('BASEPATH') OR 
    exit('No direct script access allowed');

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

    // create methods
  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 
  public function getAllNewsCount() {
        $this->db->from('news');
        return $this->db->count_all_results();
    }
  // Fetch data according to per_page limit
  public function newsList() {       
        $this->db->select(array('n.id', 'n.title', 'n.name', 'n.email', 'n.user_pic', 'n.created_date'));
        $this->db->from('news as n');          
        $this->db->limit($this->_pageNumber, $this->_offset);
        $query = $this->db->get();
        return $query->result_array();
    }

}

?>

<?php

/**

* Description of News Model pagination

* @author CodersMag Team

defined('BASEPATH') OR

exit('No direct script access allowed');

class News_model extends CI_Model {

// Declare variables

private $_limit;

private $_pageNumber;

private $_offset;

// create methods

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

public function getAllNewsCount() {

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

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

}

// Fetch data according to per_page limit

public function newsList() {

$this->db->select(array('n.id', 'n.title', 'n.name', 'n.email', 'n.user_pic', 'n.created_date'));

$this->db->from('news as n');

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

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

return $query->result_array();

}

Step 4: Create a controller file

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

The Contact controller handles the contact form process.
__construct() – Loads the required library, helper.
index() – fetch data.

<?php
/**
 * Description of News Controller for pagination
 *
 * @author CodersMag Team
 */
defined('BASEPATH') OR 
    exit('No direct script access allowed');

class News extends CI_Controller {

  //Load libraries in Constructor.
  public function __construct() {
        parent::__construct();        
        $this->load->library('pagination');
        $this->load->model('News_model', 'news');
    }
  // index method
  public function index() { 
        $data['metaDescription'] = 'Pagination Using CodeIgniter and MySQL';
        $data['metaKeywords'] = 'Pagination Using CodeIgniter and MySQL';
        $data['title'] = "Pagination Using CodeIgniter and MySQL | Coders Mag";
        $data['breadcrumbs'] = array('Pagination Using CodeIgniter and MySQL' => '#'); 
        $config['total_rows'] = $this->news->getAllNewsCount();
        $data['total_count'] = $config['total_rows'];
        $config['suffix'] = '';

        if ($config['total_rows'] > 0) {
            $page_number = $this->uri->segment(3);
            $config['base_url'] = base_url() . 'news/index/';
            if (empty($page_number))
                $page_number = 1;
            $offset = ($page_number - 1) * $this->pagination->per_page;
            $this->news->setPageNumber($this->pagination->per_page);
            $this->news->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['newsInfo'] = $this->news->newsList();
        }

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

<?php

/**

* Description of News Controller for pagination

* @author CodersMag Team

defined('BASEPATH') OR

exit('No direct script access allowed');

class News extends CI_Controller {

//Load libraries in Constructor.

public function __construct() {

parent::__construct();

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

$this->load->model('News_model', 'news');

}

// index method

public function index() {

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

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

$data['title'] = "Pagination Using CodeIgniter and MySQL | Coders Mag";

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

$config['total_rows'] = $this->news->getAllNewsCount();

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

$config['suffix'] = '';

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

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

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

if (empty($page_number))

$page_number = 1;

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

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

$this->news->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['newsInfo'] = $this->news->newsList();

}

$this->load->view('news/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>
  <link rel="canonical" href="https://www.codersmag.com/" />
  <meta name="author" content="CodersMag">  
  <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="">
  <link rel="icon" type="image/ico" href="<?php print HTTP_IMAGE_PATH; ?>favicon.ico">
  <title><?php print $title; ?></title>
  <!-- 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://codersmag.com/"><h1>Coders Mag</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://codersmag.com/">Home
                <span class="sr-only">(current)</span>
              </a>
        </li>
        <li class="nav-item">
          <a class="nav-link" href="https://codersmag.com/php-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://codersmag.com/"><h1>Coders Mag</h1></a>

</button>

<a class="nav-link" href="https://codersmag.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="small mb-4 mb-lg-0" style="color: #E6ECF0;">Copyright &copy;  2017 - <?php print date('Y', time());?> <a style="color: #E6ECF0;" href="https://codersmag.com/">CODERSMAG.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>
  <!-- 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>

<p class="small mb-4 mb-lg-0" style="color: #E6ECF0;">Copyright © 2017 - <?php print date('Y', time());?> <a style="color: #E6ECF0;" href="https://codersmag.com/">CODERSMAG.COM</a> All rights reserved.</p>

</div>

</a>

</li>

</a>

</li>

</a>

</li>

</ul>

</div>

</footer>

</body>

</html>

Step 7: Create a view(index)

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

 <?php $this->load->view('templates/header');?>
  <section class="showcase">
    <div class="container">
      <div class="pb-2 mt-4 mb-2 border-bottom">
        <h2>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 class="header" style="width: 5%">#</th>
                  <th class="header" style="width: 40%">Title</th> 
                  <th class="header" style="width: 15%">@Author</th> 
                  <th class="header" style="width: 20%">Email</th>                           
                  <th class="header" style="width: 20%">Posted On</th>
                </tr>
              </thead>
              <tbody>
                <?php if (isset($newsInfo) && !empty($newsInfo)) { ?>
                  <?php foreach ($newsInfo as $key => $element) { ?>
                  <tr>                    
                    <td><img src="<?php echo HTTP_IMAGE_PATH.$element['user_pic']; ?>" style="width: 40px; height: 40px;"></td>  
                    <td><?php echo $element['title']; ?></td>  
                    <td><?php echo $element['name']; ?></td> 
                    <td><?php echo $element['email']; ?></td> 
                    <td><?php echo date(DATE_TIME_FORMAT,$element['created_date']); ?></td> 
                  </tr>
                  <?php } ?>
                 <?php } else { ?>
                  <tr>
                    <td colspan="4">There is no record.</td>  
                  </tr>
                 <?php } ?>
              </tbody>
            </table>
          </div>      
      </div>
    <div class="row">
          <div class="col-lg-12 text-right">
            <?php if (isset($newsInfo) && is_array($newsInfo)) echo $page_links; ?>
          </div>
        </div>
    </div>
  </section>
<?php $this->load->view('templates/footer');?>

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

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

</div>

<thead>

<tr>

<th class="header" style="width: 40%">Title</th>

<th class="header" style="width: 15%">@Author</th>

<th class="header" style="width: 20%">Email</th>

<th class="header" style="width: 20%">Posted On</th>

</tr>

</thead>

<tbody>

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

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

<tr>

<td><img src="<?php echo HTTP_IMAGE_PATH.$element['user_pic']; ?>" style="width: 40px; height: 40px;"></td>

</tr>

<?php } ?>

<?php } else { ?>

<tr>

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

</tr>

<?php } ?>

</tbody>

</table>

</div>

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

</div>

</section>

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

Demo Download