sql - 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

Answer 1

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'] . "";
        print $row['color'] . "";
        print $row['calories'] . "
";
    }
}

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'] . "";
        print $row['color'] . "";
        print $row['calories'] . "
";
    }
}

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'] . "";
        print $row['color'] . "";
        print $row['calories'] . "
";
    }
}

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