What are the potential pitfalls of using while loops to display data retrieved from a MySQL database in PHP?

Using while loops to display data retrieved from a MySQL database in PHP can lead to performance issues if the dataset is large, as it retrieves all rows at once. To solve this, it's recommended to use pagination to limit the number of rows fetched at a time and improve the overall performance of the application. 

// Example pagination implementation
$limit = 10; // Number of rows to display per page
$page = isset($_GET['page']) ? $_GET['page'] : 1; // Get current page number

$offset = ($page - 1) * $limit; // Calculate offset for SQL query

$query = "SELECT * FROM table_name LIMIT $offset, $limit";
$result = mysqli_query($connection, $query);

while ($row = mysqli_fetch_assoc($result)) {
    // Display data here
}

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

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

Keywords

while loop MySQL database data retrieval PHP potential pitfalls

Related Questions