Skip to main content

SIMPLE PAGINATION USING PHP AND BOOTSTRAP

Hello guys, today we will learn how to create a simple pagination using PHP and Bootstrap.

What is pagination?

If you have a form which allows the user to browse through the rows in a database table, what do you do if that table has hundreds or even thousands of rows? It would not be a good idea to show all those rows in a single form, instead you should split the database output into more manageable chunks or 'pages'. There are two things you must do:

Decide on the maximum number of database rows that can be included in each page. You may hard code this value, or (my preferred method) you can define it in a variable so that the value may be changed at runtime.

You then need to inform the user that other 'pages' are available and provide a mechanism whereby the user is able to select a different 'page' of details. This is nothing but pagination area.
                               

This area tells the user which page is currently being viewed, the total number of pages available, and contains links to go either forwards or backwards through the available pages.

Logic behind simple pagination:

Before discussing about writing code we will discuss simple logic need to implement it.
Example : Suppose we have 100 rows in our database table. Let it be named as total_records. We want to limit the number of rows to display to a page is 10. Let it be named as limit.

 Number of  pages  we  need  to   display  the  whole   records  from    database
(total_pages = total_records/limit).

i.e.. total_pages = 100/10 = 10

This simple logic is used to display number of pages in pagination area. Now let's write the code using this logic.

How to write the code in simple way:

To include the functionality you need to follow the steps outlined below/

1.Get the required page number
This code will get the required page number from the $_GET array. Note that if it is not present it will default to 1.

1 
2
3
4
5
if (isset ( $_GET ["pageNo"] )) {
 $page = $_GET ["pageNo"];
} else {
 $page = 1;
};

2. Identify number of rows in database
This code will obtain the total number of rows in the database table

1 
2
3
4
$sql = "SELECT COUNT(id) FROM myTable";
 $rs_result = mysqli_query ( $db, $sql );
 $row = mysqli_fetch_row ( $rs_result );
 $total_records = $row [0];

3. Calculate the lastpage number or number of pages need to display data
This code use number of rows per page in order to find the number of last page

1
2 
$rows_per_page = 5;
$lastpage = ceil ( $total_records / $rows_per_page );

4. Ensure that $pageno is within range
This code checks that the value of $page is an integer between 1 and $lastpage.

1 
2
3
4
5
6
7
$pageno = (int)$page;
if ($pageno > $lastpage) {
   $pageno = $lastpage;
} // if
if ($pageno < 1) {
   $pageno = 1;
}

5. Construct LIMIT clause
This code will construct the LIMIT clause for the sql SELECT statement.

1 
$start_from = ($pageno - 1) * $rows_per_page;


6. Database query
Now we can issue the database query and process the result.

1
2 
$query = "SELECT * FROM myTable ORDER BY first_name ASC  LIMIT $start_from, $rows_per_page";
$result = mysqli_query ( $db, $query ) or die ( 'Error querying database.' );

7.Construct Pagination area hyperlinks
Finally we must construct the hyperlinks which will allow the user to select other pages.

1 
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
if ($pageno == 1) {
 echo " FIRST PREV ";
} else {
 echo " <a href='{$_SERVER['PHP_SELF']}?pageNo=1'>FIRST</a> ";
 $prevpage = $pageno - 1;
 echo " <a href='{$_SERVER['PHP_SELF']}?pageNo=$prevpage'>PREV</a> ";
} // if
echo " ( Page $pageno of $lastpage ) ";
if ($pageno == $lastpage) {
 echo " NEXT LAST ";
} else {
 $nextpage = $pageno+1;
 echo " <a href='{$_SERVER['PHP_SELF']}?pageNo=$nextpage'>NEXT</a> ";
 echo " <a href='{$_SERVER['PHP_SELF']}?pageNo=$lastpage'>LAST</a> ";
}

Entire source code:


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
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Simple PHP paginaion</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<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

// Get page value
$rows_per_page = 3;
if (isset ( $_GET ["pageNo"] )) {
 $pageno = $_GET ["pageNo"];
} else {
 $pageno = 1;
};

$start_from = ($pageno - 1) * $rows_per_page;

$db = mysqli_connect ( 'localhost', 'root', 'root', 'test' ) or die ( 'Error connecting to MySQL server.' );
$query = "SELECT * FROM myTable ORDER BY first_name ASC  LIMIT $start_from, $rows_per_page";
$result = mysqli_query ( $db, $query ) or die ( 'Error querying database.' );
?>
<div class="container">
  <table class="table table-bordered table-striped">
   <thead>
    <tr>
     <th>Firstname</th>
     <th>Lastname</th>
     <th>Email</th>
    </tr>
   </thead>
   <tbody>
 <?php
 while ( $row = mysqli_fetch_assoc ( $result ) ) {
  ?>
            <tr>
     <td><?=$row["first_name"]; ?></td>
     <td><?=$row["last_name"]; ?></td>
     <td><?=$row["email"]; ?></td>
    </tr>
  <?php
 };
 ?>
   </tbody>
  </table>
<?php
$sql = "SELECT COUNT(id) FROM myTable";
$rs_result = mysqli_query ( $db, $sql );
$row = mysqli_fetch_row ( $rs_result );
$total_records = $row [0];
$lastpage = ceil ( $total_records / $rows_per_page );
$pageno = ( int ) $pageno;
if ($pageno > $lastpage) {
 $pageno = $lastpage;
}
if ($pageno < 1) {
 $pageno = 1;
}
if ($pageno == 1) {
 echo " FIRST PREV ";
} else {
 echo " <a href='{$_SERVER['PHP_SELF']}?pageNo=1'>FIRST</a> ";
 $prevpage = $pageno - 1;
 echo " <a href='{$_SERVER['PHP_SELF']}?pageNo=$prevpage'>PREV</a> ";
}
echo " ( Page $pageno of $lastpage ) ";
if ($pageno == $lastpage) {
 echo " NEXT LAST ";
} else {
 $nextpage = $pageno + 1;
 echo " <a href='{$_SERVER['PHP_SELF']}?pageNo=$nextpage'>NEXT</a> ";
 echo " <a href='{$_SERVER['PHP_SELF']}?pageNo=$lastpage'>LAST</a> ";
}
?>
  </div>
</body>
</html>
<?php

Sample output



This is simple PHP pagination without need of any jQuery,AJAX.

Hope you like it and do try :)

Labels:

Comments

Post a Comment

Popular posts from this blog

Reading and Generating QR codes in Python using QRtools

What are QR codes? A Quick Response (QR) code is a 2 dimensional barcode that is used due to its fast readability and relatively large storage capacity.  2 dimensional barcodes are similar to one dimensional barcodes, but can store more information per unit area. Installation and Dependencies Linux:   qrtools can be installed on debian based linux systems with the following commands $sudo apt-get update $sudo apt-get install python-qrtools The following dependencies must be installed as well [sudo] pip install pypng [sudo] pip install zbar [sudo] pip install pillow Windows:   qrtools can be installed on windows by downloading the file from here(https://pypi.python.org/pypi/qrtools/0.0.1). On downloading and extraction, run the following command from inside the folder python setup.py install Generating a qrCode: qrtools contains a class QR (can be viewed in the source code), for which we must initially create an object. The object takes the ...
Post a Comment
Read more

MYSQL CONNECTION USING PDO WITH PHP

Hello guys, In the previous tutorial we have seen how to connect to MySQL database with PHP using MySQLi. Today we will learn how to connect with PDO. As a PHP programmer we have learned how to access database by using either MySQL and MySQLi. As of PHP 5.1, there's a better way. PHP Data Objects(PDO) provide methods for prepared statements and working with objects that will make you far more productive! PDO Introduction :        PDO provides a data-access abstraction layer, which means that, regardless of which database you're using, you use the same functions to issue queries and fetch data. PDO does not provide a database abstraction; it doesn't rewrite SQL or emulate missing features. Database Support:       The extension can support any database that a PDO driver has been written for. The following drivers currently implement the PDO interface: PDO_CUBRID Cubrid PDO_DBLIB FreeTDS / Microsoft SQL Server / Sybase PDO_FIREBIRD Firebird PDO_I...
4 comments
Read more