pagination in php and mysql
PHP Technical

How to Create pagination using php and mysql

Ankit

welcome back to shortlearner.com, in our previous post we learn how to create a signup or registration page with the help of AJAX, PHP and MySQL.

Now in this post today we will learn how to create a pagination using PHP and MySQL.

so most of the time we see when we are developing any kind of software and when we fetch records from our MySQL database it probably returns us thousand or millions records. so it is not a good idea to show all records on a single page, so we split all those records into multiple pages.

pagination in php and mysql

so before start this tutorial we just take an overview how we split records into multiple pages.
first of all we are establish a database connection , in this tutorial my database name is library. so i am going to fetch all the books name and their title which are stored in the table name called bookstore.

Also Read :
PHP Login Script With Remember me.
Unable to create a directory a wordpress error
Change password using javascript, php and mysqli.
Password and Confirm Password Validation Using JavaScript
Check Email is Already Registered in Database using Ajax and JavaScript.
How to hide extension of html and php file.? 

<?php
$hostname = 'localhost'; // your mysql hostname
$username = 'root';   // Your mysql db username
$password = 'rootroot';   // You mysql db password
$database = 'library';       // mysql db name
$con = mysqli_connect($hostname, $username, $password, $database);
 
    if (isset($_GET["page"])) { 
      $page  = $_GET["page"]; 
    } else { 
      $page=1; 
    };
$recordsPerPage=20; 
$start = ($page-1) * $recordsPerPage; 
$query = "SELECT * FROM bookstore LIMIT $start, $recordsPerPage"; 
$result = mysqli_query($con, $query);
 
echo "<table><tr><th>Title</th><th>Description</th></tr>";
while ($row = mysqli_fetch_assoc($result)) { 
            echo "<tr>
            <td>".$row['Title']."</td><td>".$row['Bookname']."</td></tr>";            
}
echo "</table>";
 
$query = "SELECT * FROM bookstore"; 
$result = mysqli_query($con, $query); //run the query
$totalRecords = mysqli_num_rows($result);  //count number of records
$totalPages = ceil($totalRecords / $recordsPerPage); 
 
echo "<a href='abc.php?page=1'>".'|<'."</a> "; // Go to 1st page  
 
for ($num=1; $num<=$totalPages; $num++) { 
            echo "<a href='abc.php?page=".$num."'>".$num."</a> "; 
}; 
echo "<a href='abc.php?page=$totalPages'>".'>|'."</a> "; // Go to last page
?>
</body>
</html>