The Infinite Loop Trap: Why while($row = mysql_fetch_assoc(...)) Breaks Your Cart Logic
When migrating from procedural PHP to more structured workflows, one of the most common stumbling blocks involves understanding how data iteration works inside loops. The specific scenario you encounteredâwhere your cart displays only product IDs and calculates a total of zeroâis a classic symptom of nested loop logic combined with an inefficient query pattern.
Your original working code relied on a preset array ($items). This is essentially an in-memory lookup table. You iterated through the session cart, then iterated through that static array until you found a match. It was O(n²) complexity, but it worked because the data source was finite and loaded instantly.
When you replaced that static array with a database query using mysql_fetch_assoc, you introduced two critical architectural flaws: an unbounded outer loop and a flawed inner loop condition.
The Problem with Nested While Loops Over Entire Tables
In your attempted solution, you placed the database query outside the main logic block but kept it active throughout the execution. You then nested a while loop inside a foreach. This creates a massive performance bottleneck and logical error.
Every time the script runs to display the cart, if you do not reset or close the result set properly before re-querying, or worse, if you iterate through the entire products table for every single item in the cart, you are creating an O(n²) operation against a database cursor that is never closed.
More importantly, look at this snippet:
while($row = mysql_fetch_assoc($productsSql)) {
foreach ($row as $product) {
if ($product['id'] == $id) {
// ... logic
}
}
}
Here lies the core issue. You are iterating through every column in the database row inside the foreach. If your table has columns like id, refCode, desc, pack, measure, etc., the loop runs once for each column name.
When you check if ($product['id'] == $id), you are trying to access the 'id' key of the current column's value. But $row['colName'] is a scalar string or number, not an associative array. Therefore, $product['id'] will always be undefined or null for every iteration except potentially the first one where it might accidentally match. Even if it matches, the break statement only exits the innermost loop (the column iteration), leaving the outer while loop still running through the entire rest of the products table.
Because you never break out of the while($row = ...) loop once a match is found, and because you are iterating over columns rather than rows effectively, your logic fails to accumulate the correct total. The cursor remains open or moves unpredictably depending on how PHP handles the resource, leading to stale data or empty results.
The Correct Approach: Direct Row Fetching
To fix this, you must stop treating a single row as an iterable object and start treating it as a discrete record. Furthermore, you should never iterate through an entire table just to find one matching ID if you can avoid it.
Option 1: Fixing the Procedural MySQL Loop
If you must stick with procedural mysql_* functions (which are deprecated and removed in PHP 7+), you need to fetch rows one by one or use a cursor-based approach that respects the break statement.
However, the most efficient fix is to change your inner loop structure. Instead of iterating over columns, iterate over rows until you find the ID match. You must also ensure you stop fetching once the item is found so you don't process the rest of the table unnecessarily.
$productsSql = mysql_query($productsQuery) or die(mysql_error());
// Initialize cart and total inside the request handler scope
$cart = array();
$total = 0;
if (isset($_GET['cart'])) {
foreach ($_SESSION['cart'] as $id) {
// Fetch rows until we find the matching ID or run out of results
while ($row = mysql_fetch_assoc($productsSql)) {
if ($row['id'] == $id) {
// Match found. Add to cart and calculate total immediately.
// Note: We do not need to iterate over columns here.
$cart[] = $row;
$total += $row['price1']; // Assuming price1 is the currency column
// CRITICAL: Break out of the while loop immediately after finding the match
break;
}
}
}
mysql_free_result($productsSql); // Always free the result set when done
include 'cart.html.php';
exit();
}
Option 2: The Modern Recommendation (PDO)
The underlying code uses mysql_fetch_assoc, which signals a need for migration. MySQLi or PDO are the standards. With PDO, you can fetch a single row directly by ID if your query supports it, avoiding the nested loop entirely.
If you refactor the SQL to use a WHERE clause:
SELECT id, refCode, desc, price1 FROM products WHERE id = ?
You can pass the $id from the session cart into the prepared statement. This reduces the complexity from O(n²) to O(1).
Debugging the Output
Your issue of displaying only IDs in both description and price fields stems directly from the foreach ($row as $product) line. That loop converts your associative array (e.g., ['id' => 5, 'desc' => 'Widget']) into an indexed array where keys are column names (0, 1, 2).
When you then try to access $product['id'] inside that loop, you are accessing the first key of the sub-array, which happens to be id only by coincidence or null. The data structure is flattened incorrectly. By removing the inner foreach and accessing $row['desc'] directly, you preserve the column names and retrieve the correct values for your HTML table cells.
Always remember that in PHP, an associative array row from a database query maps keys to values directly. You do not need to iterate over the keys unless you are dynamically generating HTML based on every column name, which is rarely necessary for specific data points like price or description.