How can beginners in PHP effectively use LIMIT in combination with mysql_fetch_assoc() to handle pagination and data display?

To handle pagination and data display in PHP using LIMIT with mysql_fetch_assoc(), beginners can use a combination of LIMIT in their SQL query to fetch a specific number of rows at a time and then loop through the results using mysql_fetch_assoc() to display the data on each page. By setting the LIMIT dynamically based on the current page number and the number of items to display per page, beginners can efficiently paginate through large datasets. 

<?php

// Establish a database connection
$connection = mysqli_connect("localhost", "username", "password", "database");

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

// Fetch data with LIMIT
$query = "SELECT * FROM table_name LIMIT $start_from, $items_per_page";
$result = mysqli_query($connection, $query);

// Display data
while ($row = mysqli_fetch_assoc($result)) {
    echo $row['column_name'] . "<br>";
}

// Pagination links
$total_rows = mysqli_num_rows(mysqli_query($connection, "SELECT * FROM table_name"));
$total_pages = ceil($total_rows / $items_per_page);

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

// Close the database connection
mysqli_close($connection);

?>

Keywords

PHP LIMIT mysql_fetch_assoc() pagination data display

Related Questions