What are common pitfalls when using MySQL with PHP for pagination?

One common pitfall when using MySQL with PHP for pagination is not properly handling the calculation of the LIMIT clause for each page. To solve this, you need to calculate the OFFSET value based on the current page number and the number of results per page. Additionally, you should ensure that the total number of pages is calculated correctly to prevent going beyond the available results. 

// Calculate the OFFSET value for pagination
$per_page = 10;
$page = isset($_GET['page']) ? $_GET['page'] : 1;
$offset = ($page - 1) * $per_page;

// Query to fetch results for the current page
$query = "SELECT * FROM table_name LIMIT $offset, $per_page";
$result = mysqli_query($connection, $query);

// Calculate total number of pages
$total_results = mysqli_num_rows(mysqli_query($connection, "SELECT * FROM table_name"));
$total_pages = ceil($total_results / $per_page);

// Display pagination links
for ($i = 1; $i <= $total_pages; $i++) {
    echo "<a href='?page=$i'>$i</a> ";
}

Keywords

MySQL PHP pagination LIMIT OFFSET

Related Questions