PHP Pagination

What is php pagination ?


When large amount of data is to be retrieved from database than we use php pagination.Suppose we have 1000 lines of database than it is not good practice to retrieved all data on a single page instead we can show them in chunks of pages i.e we can spread that data to multiple pages.We can retrieved  that data in segments of table.





We can access further that data by creating links to update or delete record.But before using the Php pagination let's have a look what the thing we have required for simple pagination.

Databse for php pagination


A large amount of database that we have to reterive .Here below is the database file given go to your mysql and create a database named "pagination" than click on sql tab and enter the following command for making a table


CREATE TABLE `cardemo` (
  `id` int(11) NOT NULL,
  `car_name` varchar(255) NOT NULL,   `make` varchar(255) NOT NULL,
  `model` varchar(255) NOT NULL,
  `accessories` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `cardemoADD PRIMARY KEY (`id`);
ALTER TABLE `cardemoMODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;


Now your table is ready in backend and now enter some values in this table or in sql tab enter the below command couple of times or copy past these line couple of times so that you can better understand pagination concept.


INSERT INTO `cardemo` (`id`, `car_name`, `make`, `model`, `accessories`) VALUES (NULL, 'Honda city', 'Honda', '2018', 'ABS'), (NULL, 'Jaquar', 'Jaquar', '2014', 'Music System');

Now you mysql table is ready for pagination purpose.

Logic behind php pagination 


Below is the code for php pagination save this code in your htdocs folder or on your server and make sure you have entered proper configuration in mysqli_connect() and mysqli_select function.

In pagination at the below end you can see list of pages saying number 1 , 2 .. so on what happens when you click on that number.Have you ever thought about it?.The whole functionality revolves around this click.


  • whenever a number is clicked php works 
  • How many record  you want to reterive suppose we need 5 records.
  • If we will on number 3 than how many records to be skipped? we will skip 10 reccords and from number 11 to 15 record will be shown now the thing is how we will get it we can get it using

current record on our screen = ( current page number we have clicked * record defined on each page ) - record per page

i.e  current record = ( 3 * 5 ) - 5 the ans is 10 that means we will skip 10 records if we will click on number 3.

  • what will be our last page or how many pages our total database required ?

i.e   No of pages = total number of record  /  record on each page

That's all now try to follow this code everything will be very clear to you how pagination works.
In this code we will use bootstrap cdn for basic css so you can customize it according to your need.You may copy paste this code for running purpose make sure you have provided proper database name all code on this blog are 100% tested if have any issue can comment in comment box.


<?php

//Enter your host configuration here in my case it is root

$conn = mysqli_connect('localhost', 'root', '');

if (!$conn){

    die("Database conn Failed" . mysqli_error($conn));

}

//Enter yoour database name here in my case i am using pagination.

$select_db = mysqli_select_db($conn, 'pagination');

if (!$select_db){
    die("Database Selection Failed" . mysqli_error($conn));

}
/*************************************************************/

$recordperpage = 5;
if(isset($_GET['page']) & !empty($_GET['page'])){
$currentpage = $_GET['page'];
}else{
$currentpage = 1;
}
$recordSkip = ($currentpage * $recordperpage) - $recordperpage;
$query1 = "SELECT * FROM `carDemo`";
$totalpageCounted = mysqli_query($conn, $query1);
$totalresult = mysqli_num_rows($totalpageCounted);

$lastpage = ceil($totalresult/$recordperpage);
$recordSkippage = 1; $nextpage = $currentpage + 1;
$previouspage = $currentpage - 1;
//It will select only required pages from database
$query2 = "SELECT * FROM `carDemo` LIMIT $recordSkip, $recordperpage";
$res = mysqli_query($conn, $query2);
?>

<!DOCTYPE html>
<html>
 <head><title>Simple-pagination in php</title>


 <!--  --------------bootstrap css cdn-------------------  -->
<link rel="stylesheet"
href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" >


 </head>
 <body>
 <div class="container">


 <div class="row">
 <h1>PHP PAGINATION</h1>  <table class="table ">
 <thead>
 <tr>
   <th>Id</th>
   <th>Car Name</th>
   <th>Make</th>
   <th>Model</th>
   <th>Accessories</th>
   <th>Update Records</th>
 </tr>

 </thead>
 <tbody>

 <?php

    while($r = mysqli_fetch_assoc($res)){

 ?>
    <tr>
     <th scope="row"><?php echo $r['id']; ?></th>

     <td><?php echo $r['car_name']; ?></td>

    <td
><?php echo $r['make']; ?></td>
     <td><?php echo $r['model']; ?></td>
     <td><?php echo $r['accessories']; ?></td>

     <td>

     <a href="delete.php?id=<?php echo $r['id']; ?>"><span class="glyphicon glyphicon-edit" aria-hidden="true"></span></a>

    </td>

  </tr>

    <?php } ?>

   </tbody>

  </table>

 </div>
<!--    -------------bootstrap navigation classes-----------------  -->  <nav aria-label="Page navigation">
  <ul class="pagination">
   <?php if($currentpage != $recordSkippage){ ?>     <li class="page-item">
      <a class="page-link" href="?page=<?php echo $recordSkippage ?>" tabindex="-1" aria-label="Previous">
        <span aria-hidden="true">&laquo;</span>
        <span class="sr-only">First</span>
      </a>
    </li>
    <?php } ?>
    <?php if($currentpage >= 5){ ?>
    <li class="page-item"><a class="page-link" href="?page=<?php echo $previouspage ?>"><?php echo $previouspage ?></a></li>
    <?php } ?>
    <li class="page-item active"><a class="page-link" href="?page=<?php echo $currentpage ?>"><?php echo $currentpage ?></a></li>
    <?php if($currentpage != $lastpage){ ?>
    <li class="page-item"><a class="page-link" href="?page=<?php echo $nextpage ?>"><?php echo $nextpage ?></a></li>
    <li class="page-item">
      <a class="page-link" href="?page=<?php echo $lastpage ?>" aria-label="Next">
        <span aria-hidden="true">&raquo;</span>

        <span class="sr-only">Last</span>
      </a>
     </li>
     <?php } ?>
    </ul>
   </nav>
  </div>
  </body>
 </html>




6 comments:

  1. Thanks so much, i have been searching throughout the web, google, stackoverflow, youtube tutorials, only here was i able to get the working code and procedure. Thanks a bunch.

    ReplyDelete
  2. I've been looking this kind of tutorial, easy to understand compare to others. Thank you.

    ReplyDelete
  3. Hello adam ... I tried your scriptp and it works, but I have a problem when a search page combines ... if you send a request from a form, telling him to filter for a fly, the first page presents me the list, but from the 2nd onwards it shows me all the data of the db ...

    Example: I am looking for the word work, page 1 will have the first lines of the DB work, from page 2 I see the whole dB. How can I solve?

    This is the query I use:

    $intervento= $_GET['intervento'];
    $data= $_GET['data'];
    $fermo= $_GET['fermo'];

    $query2 = "SELECT * FROM dati where (intervento LIKE '%".$intervento."%') ORDER BY data DESC LIMIT $recordSkip, $recordperpage";

    ReplyDelete