Simple Pagination using PHP/MySQLi

Submitted by: 
Language: 
Visitors have accessed this post 810 times.

In this tutorial, I'm going to show you how to create a simple pagination using PHP/MySQLi. Pagination are set of numbers wherein each numbers are assigned a page or in some cases used to divide rows of MySQL table to improve visual presentation like in this tutorial.

So, let's get started.

Creating our Database

First step is to create our database.
1. Open phpMyAdmin.
2. Click databases, create a database and name it as "pagination".
3. After creating a database, click the SQL and paste the below code. See image below for detailed instruction.

  1. CREATE TABLE `user` (
  2. `userid` INT(11) NOT NULL AUTO_INCREMENT,
  3. `firstname` VARCHAR(30) NOT NULL,
  4. `lastname` VARCHAR(30) NOT NULL,
  5. `username` VARCHAR(30) NOT NULL,
  6. PRIMARY KEY(`userid`)
  7. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

pagination

Inserting Sample Data into our Database

Next is to insert sample data into the database that we have created. We are going to use this in our sample table.

1. Click "pagination" database that we have created.
2. Click SQL and paste the code below.

  1. INSERT INTO `user` (`firstname`, `lastname`, `username`) VALUES
  2. ('neovic', 'devierte', 'nurhodelta'),
  3. ('julyn', 'divinagracia', 'julyn'),
  4. ('lee', 'ann', 'lee09'),
  5. ('tintin', 'demapanag', 'tin45'),
  6. ('dee', 'tolentino', 'deedee'),
  7. ('jaira', 'jacinto', 'jjacinto'),
  8. ('tetai', 'devi', 'tdevi'),
  9. ('tintin', 'hermosa', 'tinhermosa'),
  10. ('piolo', 'pascual', 'ppascual'),
  11. ('lee', 'bagun', 'faker'),
  12. ('barny', 'dino', 'bdino');

Creating our Connection

Next step is to create a database connection and save it as "conn.php". This file will serve as our bridge between our form and our database. To create the file, open your HTML code editor and paste the code below after the tag.

  1. <?php
  2.  
  3. //MySQLi Procedural
  4. $conn = mysqli_connect("localhost","root","","pagination");
  5. if (!$conn) {
  6. die("Connection failed: " . mysqli_connect_error());
  7. }
  8.  
  9. ?>

Creating our Pagination Code

Next step is to create our pagination. We name this as "pagination.php".

  1. <?php
  2.  
  3. include("conn.php");
  4.  
  5. $query=mysqli_query($conn,"select count(userid) from `user`");
  6. $row = mysqli_fetch_row($query);
  7.  
  8. $rows = $row[0];
  9.  
  10. $page_rows = 10;
  11.  
  12. $last = ceil($rows/$page_rows);
  13.  
  14. if($last < 1){
  15. $last = 1;
  16. }
  17.  
  18. $pagenum = 1;
  19.  
  20. if(isset($_GET['pn'])){
  21. $pagenum = preg_replace('#[^0-9]#', '', $_GET['pn']);
  22. }
  23.  
  24. if ($pagenum < 1) {
  25. $pagenum = 1;
  26. }
  27. else if ($pagenum > $last) {
  28. $pagenum = $last;
  29. }
  30.  
  31. $limit = 'LIMIT ' .($pagenum - 1) * $page_rows .',' .$page_rows;
  32.  
  33. $nquery=mysqli_query($conn,"select * from `user` $limit");
  34.  
  35. $paginationCtrls = '';
  36.  
  37. if($last != 1){
  38.  
  39. if ($pagenum > 1) {
  40. $previous = $pagenum - 1;
  41. $paginationCtrls .= '<a href="'.$_SERVER['PHP_SELF'].'?pn='.$previous.'" class="btn btn-default">Previous</a> &nbsp; &nbsp; ';
  42.  
  43. for($i = $pagenum-4; $i < $pagenum; $i++){
  44. if($i > 0){
  45. $paginationCtrls .= '<a href="'.$_SERVER['PHP_SELF'].'?pn='.$i.'" class="btn btn-default">'.$i.'</a> &nbsp; ';
  46. }
  47. }
  48. }
  49.  
  50. $paginationCtrls .= ''.$pagenum.' &nbsp; ';
  51.  
  52. for($i = $pagenum+1; $i <= $last; $i++){
  53. $paginationCtrls .= '<a href="'.$_SERVER['PHP_SELF'].'?pn='.$i.'" class="btn btn-default">'.$i.'</a> &nbsp; ';
  54. if($i >= $pagenum+4){
  55. break;
  56. }
  57. }
  58.  
  59. if ($pagenum != $last) {
  60. $next = $pagenum + 1;
  61. $paginationCtrls .= ' &nbsp; &nbsp; <a href="'.$_SERVER['PHP_SELF'].'?pn='.$next.'" class="btn btn-default">Next</a> ';
  62. }
  63. }
  64.  
  65. ?>

Creating our Sample Table

Lastly, we create our sample table. We name this as our "index.php".

  1. <?php include('pagination.php'); ?>
  2. <!DOCTYPE html>
  3. <html>
  4. <head>
  5. <link rel="stylesheet" href="<a href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css"" rel="nofollow">https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css"</a> />
  6. <script src="<a href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
  7. </head>
  8. <body>
  9. <div" rel="nofollow">https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></s...</a> class="container">
  10. <div style="height: 20px;"></div>
  11. <div class="row">
  12. <div class="col-lg-2">
  13. </div>
  14. <div class="col-lg-8">
  15. <table width="80%" class="table table-striped table-bordered table-hover">
  16. <thead>
  17. <th>UserID</th>
  18. <th>Firstname</th>
  19. <th>Lastname</th>
  20. <th>Username</th>
  21. </thead>
  22. <tbody>
  23. <?php
  24. while($crow = mysqli_fetch_array($nquery)){
  25. ?>
  26. <tr>
  27. <td><?php echo $crow['userid']; ?></td>
  28. <td><?php echo $crow['firstname']; ?></td>
  29. <td><?php echo $crow['lastname']; ?></td>
  30. <td><?php echo $crow['username']; ?></td>
  31. </tr>
  32. <?php
  33. }
  34. ?>
  35. </tbody>
  36. </table>
  37. <div id="pagination_controls"><?php echo $paginationCtrls; ?></div>
  38. </div>
  39. <div class="col-lg-2">
  40. </div>
  41. </div>
  42. </div>
  43. </body>
  44. </html>

That's it. In case you're having trouble with the database, I have included sample database in the file of this tutorial located in 'db' folder. If you have any comments or questions, feel free to comment below or message me here in sourcecodester.com via private message.


Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd> <table> <tr> <td> <th> <img> <h1> <h2> <h3> <iframe>
  • You may insert videos with [video:URL]
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <asp>, <c>, <cpp>, <csharp>, <css>, <html4strict>, <java>, <javascript>, <mysql>, <php>, <python>, <sql>, <vb>, <vbnet>. The supported tag styles are: <foo>, [foo].
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.