How to Create pagination using PHP and MySQLi
In this tutorial, we learn How to create pagination using PHP and MySQLi
File structure for this tutorial :
- config.php (Database configuration file)
- index.php (Main file)
- pagination.sql (SQL Table structure with dummy data)
First, create a table pagination. Structure of pagination:
|
1 2 3 4 5 6 |
CREATE TABLE `pagination` ( `id` int(10) NOT NULL, `FullNme` varchar(250) DEFAULT NULL, `EmpCode` int(10) DEFAULT NULL, `Department` varchar(250) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
Insert some dummy data into this table.
We will use the LIMIT clause for generating pagination.LIMIT clause uses two arguments one is OFFSET and the second one is the number of records return from the database.
Getting Current page number
We will use $_GET array for getting the current page number. The default page number will be 1.
|
1 2 3 4 5 |
if (isset($_GET['page_no']) && $_GET['page_no']!="") { $page_no = $_GET['page_no']; } else { $page_no = 1; } |
Formula for pagination
|
1 2 |
$total_records_per_page = 10; $offset = ($page_no-1) * $total_records_per_page; |
Getting the total number of pages
|
1 2 3 4 |
$result_count = mysqli_query($con,"SELECT COUNT(*) As total_records FROM pagination "); $total_records = mysqli_fetch_array($result_count); $total_records = $total_records['total_records']; $total_no_of_pages = ceil($total_records / $total_records_per_page); |
SQL Query for Pagination
|
1 |
$result ="SELECT * FROM pagination LIMIT $offset, $total_records_per_page"; |
Pagination Numbering
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 |
<ul class="pagination"> <li <?php if($page_no <= 1){ echo "class='disabled'"; } ?>> <a <?php if($page_no > 1){ echo "href='?page_no=$previous_page'"; } ?>>Previous</a> </li> <?php if ($total_no_of_pages <= 10){ for ($counter = 1; $counter <= $total_no_of_pages; $counter++){ if ($counter == $page_no) { echo "<li class='active'><a>$counter</a></li>"; }else{ echo "<li><a href='?page_no=$counter'>$counter</a></li>"; } } } elseif($total_no_of_pages > 10){ if($page_no <= 4) { for ($counter = 1; $counter < 8; $counter++){ if ($counter == $page_no) { echo "<li class='active'><a>$counter</a></li>"; }else{ echo "<li><a href='?page_no=$counter'>$counter</a></li>"; } } echo "<li><a>...</a></li>"; echo "<li><a href='?page_no=$second_last'>$second_last</a></li>"; echo "<li><a href='?page_no=$total_no_of_pages'>$total_no_of_pages</a></li>"; } elseif($page_no > 4 && $page_no < $total_no_of_pages - 4) { echo "<li><a href='?page_no=1'>1</a></li>"; echo "<li><a href='?page_no=2'>2</a></li>"; echo "<li><a>...</a></li>"; for ($counter = $page_no - $adjacents; $counter <= $page_no + $adjacents; $counter++) { if ($counter == $page_no) { echo "<li class='active'><a>$counter</a></li>"; }else{ echo "<li><a href='?page_no=$counter'>$counter</a></li>"; } } echo "<li><a>...</a></li>"; echo "<li><a href='?page_no=$second_last'>$second_last</a></li>"; echo "<li><a href='?page_no=$total_no_of_pages'>$total_no_of_pages</a></li>"; } else { echo "<li><a href='?page_no=1'>1</a></li>"; echo "<li><a href='?page_no=2'>2</a></li>"; echo "<li><a>...</a></li>"; for ($counter = $total_no_of_pages - 6; $counter <= $total_no_of_pages; $counter++) { if ($counter == $page_no) { echo "<li class='active'><a>$counter</a></li>"; }else{ echo "<li><a href='?page_no=$counter'>$counter</a></li>"; } } } } ?> <li <?php if($page_no >= $total_no_of_pages){ echo "class='disabled'"; } ?>> <a <?php if($page_no < $total_no_of_pages) { echo "href='?page_no=$next_page'"; } ?>>Next</a> </li> <?php if($page_no < $total_no_of_pages){ echo "<li><a href='?page_no=$total_no_of_pages'>Last ››</a></li>"; } ?> </ul> |
Here is the full code that we have written during this tutorial:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 |
<?php //include databse connection file include('config.php'); ?> <html> <head> <title>How to Create Pagination Using PHP and MySQLi</title> <link rel="stylesheet" href="css/bootstrap.min.css"> </head> <body> <div style="width:700px; margin:0 auto;"> <h3>How to Create Pagination Using PHP and MySQLi</h3> <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;'>Employee Code</th> <th style='width:150px;'>Department</th> </tr> </thead> <tbody> <?php if (isset($_GET['page_no']) && $_GET['page_no']!="") { $page_no = $_GET['page_no']; } else { $page_no = 1; } $total_records_per_page = 10; $offset = ($page_no-1) * $total_records_per_page; $previous_page = $page_no - 1; $next_page = $page_no + 1; $adjacents = "2"; $result_count = mysqli_query($con,"SELECT COUNT(*) As total_records FROM pagination "); $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 page minus 1 $result = mysqli_query($con,"SELECT * FROM pagination LIMIT $offset, $total_records_per_page"); while($row = mysqli_fetch_array($result)){ echo "<tr> <td>".$row['id']."</td> <td>".$row['FullNme']."</td> <td>".$row['EmpCode']."</td> <td>".$row['Department']."</td> </tr>"; } mysqli_close($con); ?> </tbody> </table> <div style='padding: 10px 20px 0px; border-top: dotted 1px #CCC;'> <strong>Page <?php echo $page_no." of ".$total_no_of_pages; ?></strong> </div> <ul class="pagination"> <li <?php if($page_no <= 1){ echo "class='disabled'"; } ?>> <a <?php if($page_no > 1){ echo "href='?page_no=$previous_page'"; } ?>>Previous</a> </li> <?php if ($total_no_of_pages <= 10){ for ($counter = 1; $counter <= $total_no_of_pages; $counter++){ if ($counter == $page_no) { echo "<li class='active'><a>$counter</a></li>"; }else{ echo "<li><a href='?page_no=$counter'>$counter</a></li>"; } } } elseif($total_no_of_pages > 10){ if($page_no <= 4) { for ($counter = 1; $counter < 8; $counter++){ if ($counter == $page_no) { echo "<li class='active'><a>$counter</a></li>"; }else{ echo "<li><a href='?page_no=$counter'>$counter</a></li>"; } } echo "<li><a>...</a></li>"; echo "<li><a href='?page_no=$second_last'>$second_last</a></li>"; echo "<li><a href='?page_no=$total_no_of_pages'>$total_no_of_pages</a></li>"; } elseif($page_no > 4 && $page_no < $total_no_of_pages - 4) { echo "<li><a href='?page_no=1'>1</a></li>"; echo "<li><a href='?page_no=2'>2</a></li>"; echo "<li><a>...</a></li>"; for ($counter = $page_no - $adjacents; $counter <= $page_no + $adjacents; $counter++) { if ($counter == $page_no) { echo "<li class='active'><a>$counter</a></li>"; }else{ echo "<li><a href='?page_no=$counter'>$counter</a></li>"; } } echo "<li><a>...</a></li>"; echo "<li><a href='?page_no=$second_last'>$second_last</a></li>"; echo "<li><a href='?page_no=$total_no_of_pages'>$total_no_of_pages</a></li>"; } else { echo "<li><a href='?page_no=1'>1</a></li>"; echo "<li><a href='?page_no=2'>2</a></li>"; echo "<li><a>...</a></li>"; for ($counter = $total_no_of_pages - 6; $counter <= $total_no_of_pages; $counter++) { if ($counter == $page_no) { echo "<li class='active'><a>$counter</a></li>"; }else{ echo "<li><a href='?page_no=$counter'>$counter</a></li>"; } } } } ?> <li <?php if($page_no >= $total_no_of_pages){ echo "class='disabled'"; } ?>> <a <?php if($page_no < $total_no_of_pages) { echo "href='?page_no=$next_page'"; } ?>>Next</a> </li> <?php if($page_no < $total_no_of_pages){ echo "<li><a href='?page_no=$total_no_of_pages'>Last ››</a></li>"; } ?> </ul> </div> </body> </html> |
View Demo ————————————————————————-
Download Full Source Code (How to Create pagination using PHP and MySQLi)
Size: 22.6 KB
Version: V1.0
