I just spent sometime making the following tutorial work (I converted it to PDO instead of mysqli): PHP + MySQL Pagination tutorial This is how it looks:
<html>
<head>
<title>Pagination</title>
<!-- Bootstrap CDN -->
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
</head>
<body>
<?php
if (isset($_GET['pageno'])) {
$pageno = $_GET['pageno'];
} else {
$pageno = 1;
}
$no_of_records_per_page = 10;
$offset = ($pageno-1) * $no_of_records_per_page;
$conn=mysqli_connect("localhost","my_user","my_password","my_db");
// Check connection
if (mysqli_connect_errno()){
echo "Failed to connect to MySQL: " . mysqli_connect_error();
die();
}
$total_pages_sql = "SELECT COUNT(*) FROM table";
$result = mysqli_query($conn,$total_pages_sql);
$total_rows = mysqli_fetch_array($result)[0];
$total_pages = ceil($total_rows / $no_of_records_per_page);
$sql = "SELECT * FROM table LIMIT $offset, $no_of_records_per_page";
$res_data = mysqli_query($conn,$sql);
while($row = mysqli_fetch_array($res_data)){
//here goes the data
}
mysqli_close($conn);
?>
<ul class="pagination">
<li><a href="?pageno=1">First</a></li>
<li class="<?php if($pageno <= 1){ echo 'disabled'; } ?>">
<a href="<?php if($pageno <= 1){ echo '#'; } else { echo "?pageno=".($pageno - 1); } ?>">Prev</a>
</li>
<li class="<?php if($pageno >= $total_pages){ echo 'disabled'; } ?>">
<a href="<?php if($pageno >= $total_pages){ echo '#'; } else { echo "?pageno=".($pageno + 1); } ?>">Next</a>
</li>
<li><a href="?pageno=<?php echo $total_pages; ?>">Last</a></li>
</ul>
</body>
</html>
But I encountered a problem: This script is paginating only for SQL query that has been predefined. But what if I want to have a "search" where the pagination script starts to work only after a user hits "search" and not with the pre-defined query of "select *"? Is it possible with the above script or I need to change it?
Create HTML form, for example
<form id="form" action="<?= $_SERVER['PHP_SELF'] ?>">
<input type="text" name="search" value="<?= !empty($_GET['search']) ?
htmlspecialchars($_GET['search']) : '' ?>">
<input type="submit" value="Search">
<input type="hidden" id="page" name="page" value="<?= !empty($_GET['page']) ?
intval($_GET['page']) : '' ?>">
<!-- add another fields here -->
</form>
...
<a href="" class="page">1</a>
<a href="" class="page">2</a>
and at the top of the script add:
if (!empty($_GET['search']) {
// write SQL queries for pagination and other form processing logic here
}
To pass page parameter in URL, include jQuery to your script and add the following code to the head section of the page:
$(document).ready(function(){
$('.page').click(function(e) {
e.preventDefault();
$('#page').val($(this).html());
$('#form').submit();
});
});
This code blocks the default action for the link, set the page value to the form input with #page id and submits it.