PHP MySQL Google Like Pagination With Source Code & Database
10-08-2017
Fetching and Listing Database Table Records in Page is Easy in PHP Mysql. But, When We have Huge Records in Database Table, We Fetch Records and Paginate it into Pagination. It is Very Simple and a Little bit Tricky.
Pagination is very Useful when we list a large number of records. We will show the records page-wise so that loading large records will not effect our page load speed. Here in the below example, I'm giving demo of how to create Goole Like pagination in PHP MySQL.
Create DataBase and Table
In my case, I'm Creating Database name as 'pagination' and Table name as 'names'. In 'names' Table, Store the names of your 20-30 friends and we are going to show all names using pagination. Open your SQL Terminal and Write the following command to create Database and Table
CREATE DATABASE pagination
CREATE TABLE names(
id INT(10) NOT NULL AUTO_INCREMENT,
name VARCHAR(200) NOT NULL ,
PRIMARY KEY (id)
);
Pagination Code Sample Using PHP & MySQL
<?php
//connection to DB
$connection = new MySQLi('localhost','root','','pagination');
// Set per page records to display
$num_record_per_page=2;
//set current page
if( isset($_GET["page"]) ){
$page = $_GET["page"];
}else{
$page=1;
}
//Count Total Records
$sql_count_rows="SELECT COUNT(1) FROM names";
$res_rows=$connection->query($sql_count_rows);
$total_rows =$res_rows->fetch_array()[0];
//Count Total Pages
$total_pages=ceil($total_rows/$num_record_per_page);
//Set start from number for sql query
$start_from = ($page-1)*$num_record_per_page;
//SQL fetch query according to page
$sql="SELECT *FROM names LIMIT $start_from,$num_record_per_page";
$rows=$connection->query($sql);
while ($result=$rows->fetch_assoc()) {
echo $result["name"];
echo "<br>";
}
?>
<style type="text/css">
ul li{display: inline;}
</style>
<ul>
<?php if ($page > 1): ?>
<li><a href="index.php?page=<?php echo $page-1 ?>"> << </a></li>
<?php endif; ?>
<?php if ($page > 3): ?>
<li ><a href="index.php?page=1">1</a></li>
<li class="dots">...</li>
<?php endif; ?>
<?php if ($page-2 > 0): ?><li ><a href="index.php?page=<?php echo $page-2 ?>"><?php echo $page-2 ?></a></li><?php endif; ?>
<?php if ($page-1 > 0): ?><li ><a href="index.php?page=<?php echo $page-1 ?>"><?php echo $page-1 ?></a></li><?php endif; ?>
<li style="background: #4ad295"><a href="index.php?page=<?php echo $page ?>"><?php echo $page ?></a></li>
<?php if ($page+1 < ceil($total_rows /$num_record_per_page)+1): ?><li ><a href="index.php?page=<?php echo $page+1 ?>"><?php echo $page+1 ?></a></li><?php endif; ?>
<?php if ($page+2 < ceil($total_rows /$num_record_per_page)+1): ?><li ><a href="<?php echo $_pgcurrent_page; ?>?page=<?php echo $page+2 ?>"><?php echo $page+2 ?></a></li><?php endif; ?>
<?php if ($page < ceil($total_rows / $num_record_per_page)-2): ?>
<li >...</li>
<li ><a href="index.php?page=<?php echo ceil($total_rows /$num_record_per_page) ?>"><?php echo ceil($total_rows /$num_record_per_page) ?></a></li>
<?php endif; ?>
<?php if ($page < ceil($total_rows / $num_record_per_page)): ?>
<li aria-label="Next"><a href="index.php?page=<?php echo $page+1 ?>"> >></a></li>
<?php endif; ?>
</ul>
Here is a Working Copy of the Download Link With the Database and PHP Source Code.
Download Zip File With Source Code & Database
