How to Implement Database Pagination and Navigation in PHP

How to Implement Database Pagination and Navigation in PHP

With the rapid growth of the internet, many websites need to display large amounts of data and provide easy-to-use page navigation for users to view data on different pages. In PHP, this functionality can be easily achieved. This article will walk you through how to use PHP in conjunction with database queries and pagination functions to implement data pagination and page navigation.

Step 1: Connect to the Database and Retrieve Data

First, we need to connect to the database and run a query to retrieve all data. Next, we will calculate the total amount of data and the total number of pages, then define the number of records displayed per page. Based on this information, we can calculate the starting position of the data for the current page, and use the SQL LIMIT and OFFSET clauses to retrieve the corresponding data.

Step 2: Set Pagination Parameters

In the code, we need to define the number of records displayed per page and the current page. For example:

<?php
// Set the number of records displayed per page
$limit = 10;
$page = isset($_GET['page']) ? $_GET['page'] : 1;  // Get the current page number, default is 1
$start = ($page - 1) * $limit;  // Calculate the starting position of the data
?>

Step 3: Query Data and Display Paginated Results

We then query the database for the data corresponding to the current page and display it:

<?php
// Query the total number of records
$total_query = "SELECT COUNT(*) as total FROM myTable";
$total_result = $conn->query($total_query);
$total_row = $total_result->fetch_assoc();
$total = $total_row['total'];  // Total number of records
<p>// Calculate the total number of pages<br>
$pages = ceil($total / $limit);</p>
<p>// Query data for the current page<br>
$query = "SELECT * FROM myTable LIMIT $start, $limit";<br>
$result = $conn->query($query);</p>
<p>// Display data<br>
if ($result->num_rows > 0) {<br>
while ($row = $result->fetch_assoc()) {<br>
echo "ID: " . $row["id"] . " - Name: " . $row["name"] . "<br>";<br>
}<br>
} else {<br>
echo "No data available";<br>
}<br>
?><br>

Step 4: Implement Pagination Navigation

Add pagination navigation to the page so that users can navigate through different pages of data:

<?php
echo "<div class='pagination'>";
<p>// Display "First" and "Previous" page buttons<br>
if ($page > 1) {<br>
echo "<a href='?page=1'>First</a>";<br>
echo "<a href='?page=" . ($page - 1) . "'>Previous</a>";<br>
}</p>
<p>// Display page numbers<br>
for ($i = 1; $i <= $pages; $i++) {<br>
echo "<a href='?page=$i'>$i</a>";<br>
}</p>
<p>// Display "Next" and "Last" page buttons<br>
if ($page < $pages) {<br>
echo "<a href='?page=" . ($page + 1) . "'>Next</a>";<br>
echo "<a href='?page=$pages'>Last</a>";<br>
}</p>
<p>echo "</div>";<br>
?><br>

Step 5: Close the Database Connection

After the pagination functionality is complete, remember to close the database connection:

<?php
$conn->close();
?>

Conclusion

With the steps above, you can easily implement data pagination and page navigation in PHP. This code simplifies the pagination process and provides a user-friendly navigation experience. If your website needs to handle large amounts of data, pagination is a crucial feature for improving user experience and website performance.