Display Pagination in CodeIgniter 3 with MySQL

Create a MySQL table

We will create a table named 'students' in MySQL database. The table structure is given below:

Table: students

This table stores details of all students.

Below are the columns in this table.

1. student_id - Primary key and auto incremented
2. first_name - First name of the student
3. last_name - Last name of the student
4. school - School name of the student
5. marks - Marks obtained by the student

CREATE TABLE `students` (
  `student_id` int(11) NOT NULL,
  `first_name` varchar(255) CHARACTER SET latin1 NOT NULL,
  `last_name` varchar(255) CHARACTER SET latin1 NOT NULL,
  `school` varchar(255) CHARACTER SET latin1 NOT NULL,
  `marks` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `students` (`student_id`, `first_name`, `last_name`, `school`, `marks`) VALUES
(1, 'Peter', 'Samuel', 'St. Jones High School', 98),
(2, 'Ram', 'Nayak', 'St. Jones High School', 87),
(3, 'Eric', 'Stewart', 'St. Paul\'s High School', 91),
(4, 'Nigel', 'Cullen', 'St. Paul\'s High School', 79),
(5, 'George', 'Sircar', 'St. Paul\'s High School', 86),
(6, 'George', 'Sircar', 'St. Paul\'s High School', 86),
(7, 'George', 'Sircar', 'St. Paul\'s High School', 86),
(8, 'George', 'Sircar', 'St. Paul\'s High School', 86),
(9, 'Nigel', 'Cullen', 'St. Paul\'s High School', 79),
(10, 'Eric', 'Stewart', 'St. Paul\'s High School', 91),
(11, 'Ram', 'Nayak', 'St. Jones High School', 87),
(12, 'Peter', 'Samuel', 'St. Jones High School', 98),
(13, 'Eric', 'Stewart', 'St. Paul\'s High School', 91),
(14, 'Ram', 'Nayak', 'St. Jones High School', 87),
(15, 'Peter', 'Samuel', 'St. Jones High School', 98),
(16, 'Peter', 'Samuel', 'St. Jones High School', 98),
(17, 'Peter', 'Samuel', 'St. Jones High School', 98),
(18, 'George', 'Sircar', 'St. Paul\'s High School', 86),
(19, 'Ram', 'Nayak', 'St. Jones High School', 87),
(20, 'George', 'Sircar', 'St. Paul\'s High School', 86),
(21, 'Eric', 'Stewart', 'St. Paul\'s High School', 91),
(22, 'Peter', 'Samuel', 'St. Jones High School', 98),
(23, 'Ram', 'Nayak', 'St. Jones High School', 87);

ALTER TABLE `students`
  ADD PRIMARY KEY (`student_id`);

ALTER TABLE `students`
  MODIFY `student_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=24;

After you create the table, verify that the table structure is the same as above and the rows are inserted in the table. There should be 23 rows in the table.

Folder structure

We will use a folder named 'pagination' under xampp/htdocs and keep all CodeIgniter folder/files in this folder along with our custom files. So, the root directory of the application is xampp/htdocs/pagination.

Controller code to create pagination (StudentController.php)

Let's see the code:

The controller gets the the data from the database and creates pagination links. index() method defines a few configuration parameters required for pagination. Pagination links are created using the pagination library's create_links() method. Also, using a model method, student rows are populated. Both these links and student data are sent to the view and the view displays the data with pagination. Let's see the controller code.

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

class StudentController extends CI_Controller {

	function __construct() {
	    parent::__construct();
	    $this->load->model('ModelStudent');
	}

	public function index(){
	//Pagination definition
		$config = array();
		$config['per_page'] = 5;
		$limit = $config['per_page'];
		$config['num_links'] = 5;
		$config['uri_segment'] = 1;
		$offset = $this->uri->segment(1, 0);
		$config['offset'] = $offset;
		$config['base_url'] = base_url();
		$config['cur_tag_open'] = ' ';
		$config['cur_tag_close'] = '';
		$config["total_rows"] = $this->ModelStudent->get_student_count();

		//View of Pagination Links
		$this->pagination->initialize($config);
		$data["pagination"] = $this->pagination->create_links();
		$data['student_data'] = $this->ModelStudent->get_student($limit, $offset);
		$this->load->view('student',$data);
	}
}   // end of controller class

The first few lines of index() method are for setting a few parameters required for the pagination library. The pagination library is added in autoload.php. It gets the total number of rows from the database and then generates a few pagination links depending on the value of the number of rows per page to be displayed.

Method get_student_count() of the model gives the total number of rows. It calls the model method get_student($limit, $offset) to get the student rows per page.

Write Model code to get data from database (ModelStudent.php)

Our model will have two methods which are used in the controller. Method get_student_count() returns the count of rows from the 'students' table. Method get_student() returns a set of students as defined by LIMIT and OFFSET values. Let's see the below code of the model:

<?php
class ModelStudent extends CI_Model {
  function __construct() {
    parent::__construct();
  }

  function get_student_count() {
    return $this->db->count_all('students');
  }

  function get_student($limit, $offset) {
    $this->db->order_by('student_id');
    $this->db->limit($limit, $offset);
    $rs = $this->db->get('students');
    return $rs->result();
  }

}  // end of model class

Our controller and model are developed. Next, let us see the view.

Create the view to display list of students with pagination

In the view, we will define an html table and display the student rows along with the pagination links.

<!DOCTYPE html>
<html lang="en">
<head>
  <title>Display Students</title>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
  <link rel="stylesheet" href="<?php echo base_url('assets/css/style.css') ?>">
</head>
<body>
	<div class="container">
		<h2>List of Students</h2>
		<div class="student-tbl table-responsive">
			<table class="table table-bordered table-hover">
				<thead>
					<tr><th>Student Id</th><th>First Name</th><th>Last Name</th><th>School</th><th>Marks</th>
					</tr>
				</thead>
				<?php if (!empty($student_data)) { 
					foreach ($student_data as $row){  ?>
						<tr>
							<td><?php echo $row->student_id; ?></td>
							<td><?php echo $row->first_name; ?></td>
							<td><?php echo $row->last_name; ?></td>
							<td><?php echo $row->school;?></td>
							<td><?php echo $row->marks;?></td>
						</tr>
					<?php }
				}
				else
					echo '<td colspan = "5"> No Students Available to display</td><tr>'; ?>
			</table>
		</div>
		<div class="page">
			<?php echo $pagination; ?>
				
		</div>
	</div>
</body>
</html>

While loading the view in the controller, dataset $student_data and $pagination were sent to the view. So, in the view, we will display them. You can see the rows are displayed in the html table using a loop. For pagination, all we have to do is display $pagination and all the links will be displayed.

There is a custom stylesheet used in the views. It is given below:

h2{
	text-align:center;
	margin-top: 20px;
}
.table{
  text-align: center;
}
table>thead{
	font-size:13px;
	background-color:#154360;
	color:#fff;
}
.student-tbl{
	width: 100%;
	margin: auto;
    height:400px;
}  
.page{
  text-align: right;
}

.page a {   
    font-weight:bold;     
    color: black;      
    padding: 4px 12px;   
    text-decoration: none;
}   
.page a.active {   
      background-color: green;
      color: white;   
  }   
.page a:hover:not(.active) {   
        background-color: blue; 
        color: white;  
  }   

Some simple styles are used here.

Our view, controller and model are developed. Now we need to do some config changes before we run the application. Let's do that in the next step.

Update Configuration files and Test the Application

Let us now update few setup files before we run the application.

1. Update autoload.php (application/config/autoload.php)

   We have to add 'database' and 'pagination' in autoload libraries.

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

   Also, 'url' is added in helper array.

   
   $autoload['helper'] = array('url');
   

2. Update config file (application/config/config.php)

   Set 'base_url' and 'index_page' in this file:

   
   $config['base_url'] = 'http://localhost/pagination/';
   

   
   $config['index_page'] = '';
   

3. Update database setup file (application/config/database.php)

   We will use a database named 'demo' in localhost. We will use the 'root' user, so we updated database.php as below:

   
   $active_group = 'default';
   $query_builder = TRUE;
   
   $db['default'] = array(
   	'dsn'	=> '',
   	'hostname' => 'localhost',
   	'username' => 'root',
   	'password' => '',
   	'database' => 'demo',
   	'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
   );
   

4. Update routes (application/config/routes.php)

   
   $route['default_controller'] = 'StudentController';
   $route['(:num)'] = 'StudentController';
   $route['404_override'] = '';
   $route['translate_uri_dashes'] = FALSE;
   

5. Update .htaccess (hypertext access) file to remove index.php from the URL

   
   RewriteEngine On
   RewriteCond %{REQUEST_FILENAME} !-f
   RewriteCond %{REQUEST_FILENAME} !-d
   RewriteRule ^(.*)$ index.php?/$1 [L]
   

Test The Application

In your XAMPP control panel Apache server and MySQL services must be running. Run localhost/pagination in the browser. You will see the list of students page displayed as below:

Click on any page number and see the rows are displayed for that page.