PHP Pagination

1. Use the LIMIT Clause With SELECT Statement in SQL to Paginate the Rows in PHP
2. Add the Previous and Next Navigation Feature in the Pagination in PHP

This article will introduce a method to perform pagination in PHP using the LIMIT clause with the SELECT statement in SQL. We will use the LIMIT clause to select the specific number of rows starting from a particular index to display as pagination.

We will also demonstrate another method to add the Previous and Next navigation feature in PHP pagination. This method only adds the additional feature to navigate the corresponding pages to the first method.

Use the LIMIT Clause With SELECT Statement in SQL to Paginate the Rows in PHP

We can use the LIMIT clause with the SELECT statement to specify the first n results to show on a page. We can provide the page number in the anchor tag as a GET method to browse through the pages. In this method, we define the number of rows to be displayed per page and retrieve all the rows from the database to calculate the total number of pages required. We can use the $_GET array with the isset() function to get the page number requested by the user.

For example, create a variable $results_per_page and store 2 in it. Use the mysqli_num_rows() function to fiind the number of rows in the database and store in the $number_of_results variable. Use the ceil() function to determine the total number of pages required to display the rows. Divide the $number_of_results variable by the $results_per_page variable inside the ceil() function. Use the $_GET superglobal variable with the isset() function to check whether the page variable has been set. If it has no been set, set the $page variable to 1. If the variable has been set, assign the value to the $page variable. Subtract the $page variable with 1 and multiply it with the $this_page_first_result variable. Store the operation in the variable $this_page_first_result. Write a SQL statement with LIMIT clause as SELECT * FROM alpha LIMIT ' . $this_page_first_result . ',' . $results_per_page. Run the query and display the results. At the end, create a for loop to loop between the $page variable and the $number_of_page. Inside the loop, echo the anchor tag and write the value of the href attribute as index.php?page'.$page. Between the anchor tags write the $page variable.

In the example below, the alpha table in the database contains six rows. After performing pagination, two rows are displayed per page. The ceil() function determines the total number of pages needed to show the rows. Initially, the $page variable is not set, so the page starts from page 1. The $this_page_first_result variable determines the page number the user is currently on. The variables $this_page_first_result denotes the first row of the page. The variable $results_per_page denotes the number of results per page. The output section displays the index.php page. When clicked on page 2, it outputs the next two rows from the database.

Example Code:

#php 7.x
<?php
$number_of_pages = ceil($number_of_results/$results_per_page);
if (!isset($_GET['page'])) {
    $page = 1;
} else {
    $page = $_GET['page'];
}
$this_page_first_result = ($page-1)*$results_per_page;
$sql='SELECT * FROM alpha LIMIT ' . $this_page_first_result . ',' . $results_per_page;
$result = mysqli_query($con, $sql);
while($row = mysqli_fetch_array($result)) {
    echo $row['id'] . ' ' . $row['value']. '<br>';
}
for ($page=1;$page<=$number_of_pages;$page++) {
    echo '<a href="index.php?page=' . $page . '">' . $page . '</a> ';
}
?>

Output:

1 A 
2 B 
1 2 3

Add the Previous and Next Navigation Feature in the Pagination in PHP

We can add some additional code snippets to the code example in the first method to provide the Previous and Next navigation feature in the pagination. We can increment and decrement the $page variable by 1 to point to the previous and the next page. We can use the incremented and decremented variables in the anchor tag to achieve the Next and the Previous features.

For example, create two variables, $prev and $next. Subtract the $page variable with one and assign the operation to the $prev variable. Similarly, add one to the $page variable and assign it to the $next variable. Echo an anchor tag saying Previous right before the for loop of the page numbers. Write the value index.php?page=' . $prev . in the href attribute of the anchor tag. In the same way, make another anchor tag for Next using the $next variable as the value of page in the href attribute.

In the output section below, it shows the second page. Clicking on the Previous takes to the first page and clicks on the Next, it brings to the third page.

Example Code:

# php 7.x
<?php
$prev = $page -1;
$next = $page +1;
echo ' <a href="index.php?page=' . $prev . '"> Previous </a> ';
for ($page=1;$page<=$number_of_pages;$page++) {
    echo '<a href="index.php?page=' . $page . '">' . $page . '</a> ';
}
echo ' <a href="index.php?page=' . $next . '"> Next </a> ';
?>

Output:

3 C 
4 D 
Previous 1 2 3 Next