Codeigniter pagination with Ajax jQuery and MySQL

Codeigniter pagination with Ajax jQuery and MySQL

Make Money With Online Affiliate Marketing
April 18, 2020
Codeigniter pagination with Ajax jQuery and MySQL

Pagination with AJAX is used to load content with pagination without reloading a website page, which makes a better user experience.
In this tutorial, I’ll show how you can create Codeigniter pagination with Ajax jQuery and MySQL.

1. Create a Table

Let’s create a table in the database with name posts.

CREATE TABLE `posts` (
`id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`title` varchar(100) NOT NULL,
`body` text NOT NULL,
`url` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

First of all, I am assuming that you are familiar with Codeigniter MVC structure, if not then do not worry and see this article Codeigniter – Basic Introduction OR see this tutorial Model-View-Controller
So let’s start with controller.

2. Controller

Create a new Post.php file in the application/controllers directory.

  • __construct() – Load url helper, pagination library and Post_model model.
  • index() – Load post_view view.
  • loadPosts() – This method call from $.ajax request.

Fetch all posts records and the total number of posts records from Post_model. Configure pagination and initialize $data Array.

Encode $data array into JSON format and return.

$this->load->library(‘pagination’);

// Load Post model
$this->load->model(‘Post_model’);
}

public function index(){
$this->load->view(‘post_view’);
}

public function loadPosts($pageNo=0){

// posts per page
$per_page = 5;

// pagination position
if($pageNo != 0){
$pageNo = ($pageNo-1) * $per_page;
}

// All records count
$count = $this->Post_model->getpostsCount();

// Get all posts by page number
$all_posts = $this->Post_model->getPostData($pageNo,$per_page);

// Pagination Configuration
$config[‘base_url’] = base_url().’Post/loadPosts’;
$config[‘use_page_numbers’] = TRUE;
$config[‘total_rows’] = $count;
$config[‘per_page’] = $per_page;

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

// Initialize $data Array
$data[‘pagination’] = $this->pagination->create_links();
$data[‘result’] = $all_posts;
$data[‘row’] = $pageNo;

echo json_encode($data);

}
}

3. Model

Create a Post_model.php file in the application/models directory.

Create these methods:

  • __construct() – Constructor.
  • getPostData() – Fetch records from posts table and use $pageNo$per_page to limit records. Return an Array of all posts records.
  • getpostsCount() – Count total records in posts table and return allcount.

Class Main_model extends CI_Model {

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

public function getPostData($pageNo,$per_page) {
$this->db->select(‘*’);
$this->db->from(‘posts’);
$this->db->limit($per_page, $pageNo);
$query = $this->db->get();

return $query->result_array();
}

// Select total records
public function getpostsCount() {

$this->db->select(‘count(*) as allcount’);
$this->db->from(‘posts’);
$query = $this->db->get();
$result = $query->result_array();

return $result[0][‘allcount’];
}

}

4. View
HTML

Create a new template post_view.php file in application/views directory.

jQuery

loadPagination() – Send AJAX request to <?php base_url(); ?>Post/loadPosts/ and pass the pageNo.

<!DOCTYPE html>
<html lang=”en”>
<head>
<title>AJAX Pagination in Codeigniter</title>
<meta charset=”utf-8″>
</head>
<body>

<!– Posts Listing –>
<table id=’postsTable’ border=’1′ width=’100%’ style=’border-collapse: collapse;’>
<thead>
<tr>
<th>SrNo</th>
<th>Title</th>
<th>Desription</th>
<th>URL</th>
</tr>
</thead>
<tbody></tbody>
</table>

<!– Pagination –>
<div id=’pagination’></div>

<!– Script –>
<script src=”https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js”></script>
<script type=’text/javascript’>
$(document).ready(function(){

// Detect pagination click
$(‘#pagination’).on(‘click’,’a’,function(e){
e.preventDefault();
var page_no = $(this).attr(‘data-ci-pagination-page’);
loadPagination(page_no);
});

loadPagination(0);

// Load pagination
function loadPagination(page_no){
$.ajax({
url: ‘<?php base_url()l ?>/Post/loadPosts/’+page_no,
type: ‘get’,
dataType: ‘json’,
success: function(response){
$(‘#pagination’).html(response.pagination);
createTable(response.result,response.row);
}
});
}

// Create table list
function createTable(result,SrNo){
SrNo = Number(SrNo);
$(‘#postsTable tbody’).empty();
for(index in result){
var id = result[index].id;
var title = result[index].title;
var description = result[index].body;
description = description.substr(0, 30) + ” …”;
var url = result[index].url;
SrNo+=1;

var tr = “<tr>”;
tr += “<td>”+ SrNo +”</td>”;
tr += “<td><a href='”+ url +”‘ target=’_blank’ >”+ title +”</a></td>”;
tr += “<td>”+ description +”</td>”;
tr += “</tr>”;
$(‘#postsTable tbody’).append(tr);

}
}
});
</script>
</body>
</html>

So I hope now you know how to use work with Codeigniter pagination with Ajax jQuery and MySQL

Manoj Patial
Manoj Patial
I am Manoj Patial, a website developer from India, I have more than 10 years of experience in website development. I developed more than 200+ website using Drupal, WordPress, PHP, Codeigniter, shopify, HubSpot and other CMS/PHP frameworks.

Leave a Reply

avatar
  Subscribe  
Notify of

Pin It on Pinterest