Simple pagination script PHP MySQLi

Free Scripts » Simple pagination script PHP and MySQLi

However, in this article, we will use HTML, CSS PHP and MySQLi to create pagination.

Steps to Create Simple Pagination Using PHP and MySQLi

1. Create a Database and Table with Dummy Data
2. Create a Database Connection
3. Get the Current Page Number
4. SET Total Records Per Page Value
5. Calculate OFFSET Value and SET other Variables
6. Get the Total Number of Pages for Pagination
7. SQL Query for Fetching Limited Records using LIMIT Clause and OFFSET
8. Showing Current Page Number Out of Total
9. Creating Pagination Buttons

Demo and download the script.
Demo Pagination script Download Pagination script

Creating Structure: In this section, we will just create the basic website structure of the pagination.

CREATE DATABASE;

 1. create a table

CREATE TABLE IF NOT EXISTS `pagination_table` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`name` varchar(250) NOT NULL,
`age` int(10) NOT NULL,
`dept` varchar(250) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

$con = mysqli_connect("localhost","root","","agernic_pagination");
if (mysqli_connect_errno()){
echo "Failed to connect to MySQL: " . mysqli_connect_error();
die();
}

3. Get the Current Page Number

if (isset($_GET['page_no']) && $_GET['page_no']!="") {
$page_no = $_GET['page_no'];
} else {
$page_no = 1;
}

4. SET Total Records Per Page Value

$total_records_per_page = 10;

5. Calculate OFFSET Value and SET other Variables

$offset = ($page_no-1) * $total_records_per_page;
$previous_page = $page_no - 1;
$next_page = $page_no + 1;
$adjacents = "2";

6. Get the Total Number of Pages for Pagination

$result_count = mysqli_query(
$con,
"SELECT COUNT(*) As total_records FROM `pagination_table`"
);
$total_records = mysqli_fetch_array($result_count);
$total_records = $total_records['total_records'];
$total_no_of_pages = ceil($total_records / $total_records_per_page);
$second_last = $total_no_of_pages - 1; // total pages minus 1

7. SQL Query for Fetching Limited Records using LIMIT Clause and OFFSET

$result = mysqli_query(
$con,
"SELECT * FROM `pagination_table` LIMIT $offset, $total_records_per_page"
);
while($row = mysqli_fetch_array($result)){
echo "<tr>
<td>".$row['id']."</td>
<td>".$row['name']."</td>
<td>".$row['age']."</td>
<td>".$row['dept']."</td>
</tr>";
}
mysqli_close($con);

create table rows

<table class="table table-striped table-bordered">
<thead>
<tr>
<th style='width:50px;'>ID</th>
<th style='width:150px;'>Name</th>
<th style='width:50px;'>Age</th>
<th style='width:150px;'>Department</th>
</tr>
</thead>
<tbody>
<!--
All your PHP Script will be here
-->
</tbody>
</table>

8. Showing Current Page Number Out of Total

<div style='padding: 10px 20px 0px; border-top: dotted 1px #CCC;'>
<strong>Page <?php echo $page_no." of ".$total_no_of_pages; ?></strong>
</div>

9. Creating Pagination Buttons

Get demo and download the script.
Demo Pagination Download Pagination script