PDO Looping

[TapeGun007]

Hey guys,

 

I just want to ask if I am doing this right because I don't want to learn bad habits.

 

To loop through a database gathering information I see a lot of "solutions" where the examples store everything into an array.  On the particular page that I am developing I don't see a need for this.  I am thinking about simply writing the code something like this:

 

 

$stmt->execute($params);
$row = $stmt->fetch(PDO::FETCH_ASSOC);
if ($row){
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
            // Display results in a table
    }
}else{
    echo "<br>No Results Found<br>";
}

 

Where a lot of examples use something more like this:

 

if ($stmt->execute()) {
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$products[] = $row;
}
}

 

I could see where storing table values into an array would make sense in some/many cases or is this a standard way of pulling database data with PDO?  Doesn't it just really depend on what you need on the page?

[Jacques1]

Neither code snippet makes much sense. The first one completely skips the first row, which is probably not what you want. And the second one is a nonsensical reimplementation of the standard fetchAll() method.

 

If you just want to iterate over the result set, use a foreach loop, optionally with a flag to check for an empty set:

<?php

$hasProducts = false;
foreach ($productsStmt as $product)
{
    $hasProducts = true;

    // display $product
}
if (!$hasProducts)
{
    echo 'No products found.';
}

Modern template engines like Twig or Smarty have a specialized for-else statements which can do all that in one go.

[TapeGun007]

Ah thanks Jacques1, you probably saved me some time there!

 

And this is why I asked, because the 2nd one didn't make sense to me either.  The first one was just my initial (failed) attempt at better understanding the differences in PDO coming from mySQLi.

[Barand]

 

if ($stmt->execute()) {

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

$products[] = $row;

}

}

 

The times when you might want similar to that is when you want to specify the array indexes For example

if ($stmt->execute()) {
  while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $products[ $row['user_id'] ] = $row;
  }
}

or to group results

if ($stmt->execute()) {
  while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $products[ $row['cat_id'] ][] = $row;
  }
}