Ajax Pagination Using CodeIgniter and MySQL
Pagination is one of the most frequently used features for web applications. In this post, We have share how to implement Ajax Pagination Using CodeIgniter and MySQL and enhance it further as per our need. Pagination is the process of dividing a document into discrete pages. This is a very simple example, you can just copy paste, and change according to your requirement.
Before started to implement the Ajax Pagination Using CodeIgniter and MySQL, look files structure:
- ajax-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
- employee
- config
- system
- index.php
- vendor
- assets
- css
- style.css
- application
Step 1: Create the database and Table
For this tutorial, you need a MySQL database with the following table:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
//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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
<?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'] = '« 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
<?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.
1 2 3 4 |
<?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 employee data.loadData()
– Ajax request and get employee data.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
<?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'] = 'Ajax Pagination Using CodeIgniter and MySQL'; $data['metaKeywords'] = 'Ajax Pagination Using CodeIgniter and MySQL'; $data['title'] = "Ajax Pagination Using CodeIgniter and MySQL - TechArise"; $data['breadcrumbs'] = array('Ajax Pagination Using CodeIgniter and MySQL' => '#'); $this->load->view('employee/index', $data); } // loadData public function loadData() { $data = array(); $config['total_rows'] = $this->employee->getAllEmployeeCount(); $data['total_count'] = $config['total_rows']; $config['suffix'] = ''; if ($config['total_rows'] > 0) { $page_number = $this->input->post('pageNum'); if ($page_number > 0) { $config['base_url'] = base_url() . 'employee/loadData'; } else { $config['base_url'] = base_url() . 'employee/loadData/'; } 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->output->set_header('Content-Type: application/json'); echo json_encode($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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
<?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> |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
<!-- 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">⋅</li> <li class="list-inline-item"> <a href="#">Contact</a> </li> <li class="list-inline-item">⋅</li> <li class="list-inline-item"> <a href="#">Terms of Use</a> </li> <li class="list-inline-item">⋅</li> <li class="list-inline-item"> <a href="#">Privacy Policy</a> </li> </ul> <p class="text-muted small mb-4 mb-lg-0">Copyright © 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> |
Step 7: Create views
Create a views file named index.php inside “application/views/employee” folder.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 |
<?php $this->load->view('templates/header');?> <section class="showcase"> <div class="container"> <div class="pb-2 mt-4 mb-2 border-bottom"> <h2>Ajax Pagination Using CodeIgniter and MySQL</h2> </div> <div class="row"> <div class="col-lg-12 col-md-12 mb-12"> <table id='employeeList' 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> </tbody> </table> </div> </div> <div class="row"> <div class="col-lg-12 text-right" id="pagination_page_link"></div> </div> </div> </section> <?php $this->load->view('templates/footer');?> <script type='text/javascript'> jQuery(document).ready(function() { createPagination(0); jQuery('#pagination_page_link').on('click','li.page-item a',function(e){ e.preventDefault(); var pageNum = $(this).attr('data-ci-pagination-page'); createPagination(pageNum); }); // create Pagination function createPagination(pageNum){ jQuery.ajax({ type: 'POST', url:baseurl+'Employee/loadData', data:{pageNum:pageNum}, dataType: 'json', success: function(json) { jQuery('#pagination_page_link').html(json.page_links); paginationData(json.employeeInfo); } }); } // pagination data function paginationData(data) { jQuery('#employeeList tbody').empty(); for(key in data) { var empRow = "<tr>"; empRow += "<td>"+ data[key].name +"</td>"; empRow += "<td>"+ data[key].email +"</td>" empRow += "<td>"+ data[key].salary +"</td>" empRow += "<td>"+ data[key].age +"</td>" empRow += "</tr>"; jQuery('#employeeList tbody').append(empRow); } } }); </script> |
Step 8: Make Ajax Request and Load Data with Pagination
We will also need to use jQuery code in views
index.php
in "application/views/employee"
directory to load pagination and make Ajax request to load more records.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
<script type='text/javascript'> jQuery(document).ready(function() { createPagination(0); jQuery('#pagination_page_link').on('click','li.page-item a',function(e){ e.preventDefault(); var pageNum = $(this).attr('data-ci-pagination-page'); createPagination(pageNum); }); // create Pagination function createPagination(pageNum){ jQuery.ajax({ type: 'POST', url:baseurl+'Employee/loadData', data:{pageNum:pageNum}, dataType: 'json', success: function(json) { jQuery('#pagination_page_link').html(json.page_links); paginationData(json.employeeInfo); } }); } // pagination data function paginationData(data) { jQuery('#employeeList tbody').empty(); for(key in data) { var empRow = "<tr>"; empRow += "<td>"+ data[key].name +"</td>"; empRow += "<td>"+ data[key].email +"</td>" empRow += "<td>"+ data[key].salary +"</td>" empRow += "<td>"+ data[key].age +"</td>" empRow += "</tr>"; jQuery('#employeeList tbody').append(empRow); } } }); </script> |