php - This PDO::FETCH_ASSOC` query skips the 1rst result that's returned

Question

I'm transitioning over to PDO prepared statements, and I'm having trouble with the syntax for a basic SELECT query with a WHILE statement.

The foreach statement below echos the correct results, but the PDO::FETCH_ASSOC query is skipping the 1rst result that's returned (so it always echo's one result less than it should).

PDO::FETCH_ASSOC

$stmt = $conn->prepare("SELECT * FROM products"); 
$stmt->execute();
$row = $stmt->fetch();
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) { 
    echo $row['product_name'].' '.$row['price'].' '.$row['color'], "<br />"; 
}

foreach

foreach($conn->query('SELECT * FROM products') as $row) {
    echo $row['product_name'].' '.$row['price'].' '.$row['color'], "<br />";  
}

Answer 1

You already fetched the first row before the while loop $row = $stmt->fetch();. If you remove this line, it will work as expected.

Since the while loop will overwrite $row on each iteration, it looks like you start with the second row, but what happens is the value of $row at the first while loop iteration is overwritten.

To have the loop work the way you have written, you would need to use a do-while construct:

$row = $stmt->fetch();
do {
     echo $row['product_name'].' '.$row['price'].' '.$row['color'], "<br />";
} while($row = $stmt->fetch(PDO::FETCH_ASSOC));

Here the value of $row will be printed first, before it is overwritten by the while condition.

In this particular case I don't want to echo anything when there aren't any results

If that's the case, then check to see if your query returned any results first. Here I'm being explicit in the check, because if you removed the outer if, your while loop would still follow your intentions - that is, it won't echo anything if there aren't any results.

However, it is always good to have clear intent in your code:

if ($stmt->columnCount()) {
   while($row = $stmt->fetch(PDO::FETCH_ASSOC)) { 
       echo $row['product_name'].' '.$row['price'].' '.$row['color'], "<br />"; 
   }
}