Building Responsive Tables from Database Queries in PHP
Constructing a grid layout directly from a relational database query requires careful coordination between data retrieval logic and DOM structure. The code snippet you provided attempts to render three images per row by calculating the total number of records, dividing that count by three to determine the number of rows needed, and then iterating through nested loops. While the intent is clear, this approach relies on a fundamental misunderstanding of how mysqli_fetch_array operates within a loop.
The core issue lies in the placement of the while($row = mysqli_fetch_array($result)) statement inside the inner for loop. The mysqli_query function returns a result set containing all rows from the database. The function mysqli_fetch_array is designed to be called repeatedly until no more data remains; it does not automatically reset or jump between rows based on loop indices.
In your current implementation, when the outer loops begin execution:
- The first iteration of the inner loop executes the
whilestatement. - This consumes the first row from the result set and displays it.
- On the very next call to
mysqli_fetch_array, there is no new data in the buffer because the previous call advanced the cursor to the end of the dataset (or returned false if only one row existed).
Consequently, every subsequent <td> cell after the first one in that row will be empty. Furthermore, once the inner loop finishes its iterations for the first row, the outer loops proceed to the next iteration without re-initializing the data fetch mechanism, leading to an incomplete grid where only the top-left corner might contain data if your logic doesn't account for cursor position.
Correcting the Fetch Logic
To render a 3-column grid correctly, you must iterate through every single record in the database sequentially and assign them to table cells as they are fetched. You do not need to pre-calculate the number of rows or use nested loops that rely on index arithmetic. Instead, use a simple foreach loop over the result set.
Here is a corrected approach using modern PHP practices and PDO for better security and error handling:
<?php
// Database configuration
$host = "localhost";
$username = "root";
$password = "";
$db_name = "fruits_db";
try {
// Establish connection
$pdo = new PDO("mysql:host=$host;dbname=$db_name", $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Query for fruits
$stmt = $pdo->query("SELECT fruit_image, fruit_name FROM fruits_tbl");
// Start HTML table structure
echo '<table border="1" style="width: 100%;">';
echo '<tr>'; // First row in the grid
foreach ($stmt as $fruit) {
echo '<td width="200" height="250">';
echo '<div class="fruit_image"><img src="' . htmlspecialchars($fruit['fruit_image']) . '"></div>';
echo '<div class="fruit_title">' . htmlspecialchars($fruit['fruit_name']) . '</div>';
echo '</td>';
// Logic to close the row after every 3rd item
if ($stmt->rowCount() > 0) {
// We need a counter to know when to close <tr>
// However, doing this inside a foreach requires tracking index or using a separate counter variable.
}
}
} catch (PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
// Better implementation with explicit row counting
?>
<table border="1">
<?php
$stmt = $pdo->query("SELECT fruit_image, fruit_name FROM fruits_tbl");
$row_count = 0;
while ($fruit = $stmt->fetch(PDO::FETCH_ASSOC)) {
// Increment row count every time we fetch a new item
$row_count++;
echo '<tr>';
// Check if this is the start of a new row (every 3rd item starts a new visual row in CSS terms,
// but logically we build rows as we go. Let's adjust: we want 3 cols per HTML tr).
// Actually, the standard way is to check if we are at the beginning of a row.
// But since we fetch linearly, we can't easily know "start of row" without modulo.
// Let's restructure:
echo '<td width="180px" height="200px">';
echo '<div class="fruit_image"><img src="' . htmlspecialchars($fruit['fruit_image']) . '"></div>';
echo '<div class="fruit_title">' . htmlspecialchars($fruit['fruit_name']) . '</div>';
echo '</td>';
// Close the <tr> tag if we have placed 3 items in this row
if ($row_count % 3 == 0) {
echo '</tr>';
}
}
// Close any remaining open row if total count wasn't divisible by 3
if ($row_count % 3 != 0) {
echo '</tr>';
}
?>
</table>
Key Implementation Details
- Sequential Fetching: The
whileloop must consume the entire result set linearly. Do not nestwhileloops insideforloops unless you are explicitly managing a cursor reset, which MySQLi/PDO cursors generally do not support natively without fetching all data first into an array. - Row Closure Logic: The HTML table structure requires closing every
<tr>after exactly three<td>elements. Using the modulo operator (%) on a running counter is the most reliable way to handle this without complex nested iterations. - Security Considerations: Always use
htmlspecialchars()when outputting user data like names or image paths into HTML attributes or text content. This prevents XSS vulnerabilities if any database value contains script tags or special characters. While not strictly necessary for static images, it is a critical best practice in production applications.
Handling Edge Cases
Your original code assumed that $num_rows would always be divisible by 3. In real-world scenarios, you might have 10 items. Dividing by 3 gives 3.33, which truncates to 3 rows. Your loop would run only 3 times, leaving the last item orphaned or missing entirely depending on how the array was sliced.
The corrected logic using % handles any number of items gracefully. Whether you have 2 fruits or 50, the script will output valid HTML without breaking the table structure. If the final row contains only one or two cells, the browser will simply render them aligned to the left, which is standard behavior for tables unless CSS flexbox or grid layouts are applied to force equal distribution.
For larger applications managing complex grids, consider moving the layout logic into a Blade template or view if you are using Laravel. This separates the data retrieval (Controller) from the presentation (View), making the code more maintainable than embedding raw HTML generation directly within the script file.