Hi friends, I’m back with another tutorial. Today in this example, I'll show you to make pagination in PHP with the previous and next hyperlinks features.
Using the pagination technique we divide our MySQL big data into a few pages to make it easy for users to navigate through the website's content. It is most frequently used as the page numbers at the bottom of the page. e.g. Google's page numbers when you are searching for some result.
I've only made one PHP file and I've retrieved MySQL records in this file and set up all records in the format of a page number. For example 1, 2, 3 including with previous and next hyperlink. Let’s dive into PHP pagination codes.
Make Database & Table
First making the database called "pagination_db" and inject the below SQL code into your PhpMyAdmin to a successful user table.
CREATE TABLE `tbl_users` (
`id` int(11) NOT NULL,
`username` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;Next, we insert the 9 records below into the table using the following code. Once we apply PHP pagination codes below all records will be shown in the format of page number including with previous and next hyperlink.
INSERT INTO `tbl_users` (`id`, `username`) VALUES
(1, 'Steve Jobs'),
(2, 'Bill Gates'),
(3, 'Mark Zuckerberg'),
(4, 'Larry Page'),
(5, 'larry ellison'),
(6, 'Michael Dell'),
(7, 'Sandy Lerner'),
(8, 'James Gosling'),
(9, 'Rasmus Lerdorf');dbconfig.php
Within this file, we create a connection to the database with the PDO extension. When the connection to the database goes wrong then a specific issue will appear below the code.
<?php
$db_host="localhost"; //localhost server
$db_user="root"; //database username
$db_password=""; //database password
$db_name="pagination_db"; //database name
try
{
$db=new PDO("mysql:host={$db_host};dbname={$db_name}",$db_user,$db_password);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOEXCEPTION $e)
{
$e->getMessage();
}
?>Bootstrap – Here I use the bootstrap classes for better UI experiences in pagination code.
index.php
In this file, we set up all records in table format and implementing PHP pagination codes. I avoid code design, and I just put the pagination code.
Below code, you can quickly grasp the logic of pagination codes please jump to logic explanation.
<table class="table table-striped table-bordered table-hover">
<thead>
<tr>
<th>ID</th>
<th>Username</th>
</tr>
</thead>
<tbody>
<?php
require_once "dbconfig.php";
if(isset($_GET['page']))
{
$page=$_GET['page'];
}
else
{
$page = 1;
}
$limit = 3;
$offset=($page-1)*$limit;
$select_stmt=$db->prepare("SELECT * FROM tbl_users ORDER BY id ASC LIMIT ?,? ");
$select_stmt->bindValue(1, $offset, PDO::PARAM_INT);
$select_stmt->bindValue(2, $limit, PDO::PARAM_INT);
$select_stmt->execute();
while($row=$select_stmt->fetch(PDO::FETCH_ASSOC))
{
?>
<tr>
<td><?php echo $row['id']; ?></td>
<td><?php echo $row['username']; ?></td>
</tr>
<?php
}
?>
</tbody>
</table>
<?php
$select_stmt=$db->prepare("SELECT * FROM tbl_users");
$select_stmt->execute();
$total_records=$select_stmt->fetchAll(PDO::FETCH_ASSOC);
if($select_stmt->rowCount() > 0)
{
$total_page=ceil(count($total_records) / $limit);
echo '<ul class="pagination">';
if($page > 1)
{
echo '<li><a href="index.php?page='.($page - 1).'">Previous</a></li>';
}
for($i=1; $i<=$total_page; $i++)
{
if($i == $page){
$active = "active";
}
else{
$active = "";
}
echo '<li class="page-item '.$active.' ">
<a class="page-link" href="index.php?page='.$i.'">'.$i.'</a>
</li>';
}
if($total_page > $page)
{
echo '<li><a href="index.php?page='.($page + 1).'">Next</a></li>';
}
echo '</ul>';
}
?>Logic Explanation:
Row no 14 to 21 – With the help of PHP superglobal array variable $_GET that code will get the current page number. Consider that it will set the default page number to 1 when it's not present.
if(isset($_GET['page']))
{
$page=$_GET['page'];
}
else
{
$page = 1;
}Row no 23 – $limit variable holds the maximum records per page you want to view. We'll show 3 records per page here.
But, by setting the number of $limit variable, you can always maintain the number of records to display from a page.
$limit = 3;Row no 25 – $offset variable shows the total records which should be skipped before the first line. Our PHP script dynamically calculates this value using the formula $offset=($page-1) * $limit;.
$offset=($page-1)*$limit;Row no 27 to 40 – In prepare() statement, apply PDO select query with LIMIT clause. The LIMIT clause takes two arguments.
The bindValue() function binds the value of the arguments in the query to the LIMIT clause question (?). Variable $offset and $limit hold both value.
The execute() function executes the statement and displays and locates all records in table format from within while loop.
calculation :
$offset =0 and $imit = 3.
“SELECT * FROM tbl_users ORDER BY id ASC LIMIT 0, 3”
$select_stmt=$db->prepare("SELECT * FROM tbl_users ORDER BY id ASC LIMIT ?,? ");
$select_stmt->bindValue(1, $offset, PDO::PARAM_INT);
$select_stmt->bindValue(2, $limit, PDO::PARAM_INT);
$select_stmt->execute();
while($row=$select_stmt->fetch(PDO::FETCH_ASSOC))
{
?>
<tr>
<td><?php echo $row['id']; ?></td>
<td><?php echo $row['username']; ?></td>
</tr>
<?php
}
?>Note – LIMIT clause helps us to create a feature of pagination It uses two arguments.
The first argument is an offset value is the number of records that can be skipped before the first row. And the second argument sets the maximum number of records that will be returned from the database to be displayed per page.
Note – The explicit data type for the parameter using the PDO:: PARAM_*constants. ( Source php.net ).
Here we use PDO:: PARAM_INT.
Row no 47 to 50 – Apply the select PDO query in the prepare() statement and select all records from the database.
PDOStatement:: fetch method returns a row from the result set. PDO:: FETCH_ASSOC parameter informs PDO to return array value indexed by table column. The $total_records is an array.
$select_stmt=$db->prepare("SELECT * FROM tbl_users");
$select_stmt->execute();
$total_records=$select_stmt->fetchAll(PDO::FETCH_ASSOC);Row no 53 – The number of rows returnable by rowCount() function is greater than zero (> 0) if condition check returns.
if($select_stmt->rowCount() > 0)
{Row no 55 – We will divide the total records from the table with the rows we want to view per page to get the total number of pages.
Then, using PHP ceil() function, round the resulting value to the nearest integer see code below:
$total_page=ceil(count($total_records) / $limit);example :
In database total of 9 records available.
ceil( count( 9 ) / 3 ) = 3. That value assign to $total_page variable.
count() – The count function returns all elements or values in an array. ( Source php.net )
Row no 57 – Using echo we output < ul > tag, I use a bootstrap class here in this tag.
echo '<ul class="pagination">';Row no 59 to 62 – Here if condition, the variable $page indicates the current page number. After that code will be minus 1 from the variable, to get the previous page.
So, when we're on page number 2, we'll get an output of the formula ( 2-1) and it will be the previous page to appear in the link.
Note – although, that it will only show the previous page when a value is greater than ( > ) or equal to (==) 1.
if($page > 1)
{
echo '<li><a href="index.php?page='.($page - 1).'">Previous</a></li>';
} Row no 64 to 78 – Apply for loop(), Within this loop, we assign starting value of $i variable 1, check the total number of pages values big compare to $i, and we increment the $i value.
Here the variable $total_page returns the total value of the pages we already count above using the functions ceil( ) and count().
If condition check the $i variable and $page variable value match, we assign bootstrap class “active” to $active variable.
else condition, assign a blank string in the $active variable.
Using echo we embed $active variable in < li > tag because the active page number is recognized better. And $i variable embedded inside and outside under anchor tag.
for($i=1; $i<=$total_page; $i++)
{
if($i == $page){
$active = "active";
}
else{
$active = "";
}
echo '<li class="page-item '.$active.' ">
<a class="page-link" href="index.php?page='.$i.'">'.$i.'</a>
</li>';
}Row no 80 to 83 – if condition checks total page value greater compare to $page value.
We add it to the $page variable to get to the next page. And we need to also keep in mind that the value of $page that we attach to the page URL parameter is not greater than the total pages we have calculated our PHP code ( $total_page > $page ).
if($total_page > $page)
{
echo '<li><a href="index.php?page='.($page + 1).'">Next</a></li>';
}Row no 85 – Using echo we closed < /ul > tag.
echo '</ul>';Output:
Frequently Asked Questions.
Download Codes
No comments:
Post a Comment