Convert PHP while loop to use PDO

Question

I'm currently updating my app by switching to PDO. I have the following code:

$stmt = $db->prepare("select * from `product` where productid in (:productidLst)");
$stmt->bindParam(":productidLst",$productidLst, PDO::PARAM_INT);
$stmt->execute();

The var $productidLst is 1,2 after the above code I would like to use the PDO equivalent of this:

while($rs=mysql_fetch_assoc($res)){
    $rs['qty']=$_SESSION['basket'][$rs['productid']];
    $rs['total'] += $rs['qty']*$rs['price'];
    $total += $rs['total'];
    $a[] = $rs;
}

I have tried numerous combinations but not been successful so any help with this would be appreciated (in the 2nd code block $res was the sql). Secondly I have set the Parameter $productidLst to INT is this correct or should it be a string?

--------------------UPDATE 1----------------------------------------------------

I have tried the following code:

$stmt = $db->prepare("select * from `product` where productid in (:productidLst)");
foreach ($stmt->execute(array(':productidLst' => $productidLst)) as $row) 
{
    $total += $row['total'];
}

Which returns: Invalid argument supplied for foreach() error

At this point, you can't bind a [list value to a query parameter](http://stackoverflow.com/questions/920353/php-pdo-can-i-bind-an-array-to-an-in-condition). — outis, Aug 12 '11 at 18:42
`PDOStatement::execute()` returns a boolean which is not suitable for use in `foreach` — Phil, Aug 15 '11 at 05:35

score 18 · Accepted Answer · edited May 23 '17 at 12:34

The standard documentation in the PHP manual is usually pretty helpful. There is an example of executing a for loop with PDO in the PHP manual, PDO Details.

function getFruit($conn) {
    $sql = 'SELECT name, color, calories FROM fruit ORDER BY name';
    foreach ($conn->query($sql) as $row) {
        print $row['name'] . "\t";
        print $row['color'] . "\t";
        print $row['calories'] . "\n";
    }
}

With a few changes, the example can be made to use a prepared statement.

function getFruit($conn) {
    $query = $conn->prepare('SELECT name, color, calories FROM fruit WHERE kind=:kind ORDER BY name');
    $query->execute(array(':kind' => 'drupe'));
    // alternatively you could use PDOStatement::fetchAll() and get rid of the loop
    // this is dependent upon the design of your app
    foreach ($query as $row) {
        print $row['name'] . "\t";
        print $row['color'] . "\t";
        print $row['calories'] . "\n";
    }
}

You can also use a while loop and PDOStatement::fetch to get each row.

function getFruit($conn) {
    $query = $conn->prepare('SELECT name, color, calories FROM fruit WHERE kind=:kind ORDER BY name');
    $query->execute(array(':kind' => 'drupe'));
    // alternatively you could use PDOStatement::fetchAll() and get rid of the loop
    // this is dependent upon the design of your app
    while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
        print $row['name'] . "\t";
        print $row['color'] . "\t";
        print $row['calories'] . "\n";
    }
}

The PHP manual remains quite helpful in providing all the necessary information to create the latter two versions.

Explanation of the last version: assuming $conn is a valid PDO object. $conn->prepare($sql) returns a PDOStatement object if successful, false on failure OR an exception based on your error handling. So, assuming success we would want to actually get the data from the object. We can use $query->fetch() in a loop or $query->fetchAll() to get the data dependent upon your app. Passing in the class constant PDO::FETCH_ASSOC will return, you guessed it, an associative array of data.

Functionally, the foreach and while implementations are equivalent. Conceptually, a foreach is more appropriate, as a while loop has connotations of looping while a static condition holds, whereas foreach loops over elements of a collection. Read "Differences between a while loop and a for loop in PHP?" for part of the story.

Be sure to read the php.net reference on PDO

Hi thanks for the responses, I am still struggling, I keep getting : Invalid argument supplied for foreach():. Please see orginal post under update 1 section for the code that I am using. (Note I slightly simplified the code within the while loop for ease of reading) — Dino, Aug 13 '11 at 02:39
@outis I just edited it myself with the appropriate code changes and an explanation. — Charles Sprayberry, Aug 14 '11 at 16:47
@Charles: how was the earlier code edit inappropriate? `PDOStatement` implements `Traversable`, and the default fetch mode is `PDO::FETCH_BOTH`. Rob's point was that the PHP manual has an example that basically answers Dino's question. Switching from a `foreach` to a `while` detracts from this point. — outis, Aug 14 '11 at 21:31
@outis That was my mistake, I was not aware that `PDOStatement` was `Traversable`. If you want, you can revert my edit. I personally like this approach better, but to each their own. Thanks for the info about `Traversable` though. — Charles Sprayberry, Aug 14 '11 at 22:28
@outis Looks good. I'm sure it will be quite helpful for those of us, including myself, who have only ever seen PDOStatements used with a `while` loop. — Charles Sprayberry, Aug 15 '11 at 05:46

score 2 · Answer 2 · answered Aug 13 '11 at 09:30

You should be using PDOStatement::fetch() to fetch the row. It fetches (by default) both numerically and associatively. You can change that as well.

With your code:

while($rs=$stmt->fetch()){
    $rs['qty']=$_SESSION['basket'][$rs['productid']];
    $rs['total'] += $rs['qty']*$rs['price'];
    $total += $rs['total'];
    $a[] = $rs;
}

Manual Reference.

Convert PHP while loop to use PDO

2 Answers2

Linked

Related