What best practices should be followed when using PHP for pagination of database results?

When using PHP for pagination of database results, it is important to limit the number of results fetched from the database at a time to improve performance and user experience. One common approach is to use SQL's LIMIT and OFFSET clauses to fetch a subset of results based on the current page number and number of items per page. 

// Define pagination variables
$items_per_page = 10;
$page = isset($_GET['page']) ? $_GET['page'] : 1;
$offset = ($page - 1) * $items_per_page;

// Fetch data from database with pagination
$query = "SELECT * FROM your_table LIMIT $items_per_page OFFSET $offset";
$result = mysqli_query($connection, $query);

// Display results
while ($row = mysqli_fetch_assoc($result)) {
    // Display each row of data
}

// Display pagination links
$total_results = // Get total number of results from database
$total_pages = ceil($total_results / $items_per_page);

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

Keywords

pagination database results PHP LIMIT OFFSET

Related Questions