将 PHP while 循环转换为使用 PDO

Convert PHP while loop to use PDO

提问by Dino

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

我目前正在通过切换到 PDO 来更新我的应用程序。我有以下代码：

$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:

在上面的代码之后 var $productidLst 是 1,2 我想使用 PDO 等价物：

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?

我尝试了多种组合，但都没有成功，因此将不胜感激（在第二个代码块中，$res 是 sql）。其次，我已将参数 $productidLst 设置为 INT 这是正确的还是应该是字符串？

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

--------------------更新 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

返回：为 foreach() 错误提供的无效参数

回答by RobB

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.

PHP 手册中的标准文档通常很有帮助。在 PHP 手册PDO Details 中有一个使用 PDO 执行 for 循环的示例。

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 whileloop and PDOStatement::fetchto get each row.

您还可以使用while循环并PDOStatement::fetch获取每一行。

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.

PHP 手册在提供创建后两个版本所需的所有信息方面仍然非常有帮助。

Explanation of the last version: assuming $connis a valid PDO object. $conn->prepare($sql)returns a PDOStatementobject if successful, falseon failure ORan 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_ASSOCwill return, you guessed it, an associative array of data.

上一个版本的解释：假设$conn是一个有效的 PDO 对象。 如果成功，则$conn->prepare($sql)返回一个PDOStatement对象，false失败或基于您的错误处理的异常。因此，假设成功，我们希望实际从对象中获取数据。我们可以$query->fetch()在循环中使用或$query->fetchAll()获取依赖于您的应用程序的数据。PDO::FETCH_ASSOC你猜对了，传入类常量将返回一个关联的数据数组。

Functionally, the foreachand whileimplementations are equivalent. Conceptually, a foreachis more appropriate, as a whileloop has connotations of looping while a static condition holds, whereas foreachloops over elements of a collection. Read "Differences between a while loop and a for loop in PHP?" for part of the story.

在功能上，foreach和while实现是等效的。从概念上讲， aforeach更合适，因为while循环具有循环的含义，而静态条件成立，而foreach循环则遍历集合的元素。阅读“ PHP 中的 while 循环和 for 循环之间的区别？”了解部分故事。

Be sure to read the php.net reference on PDO

请务必阅读PDO 上的php.net 参考

回答by Madara's Ghost

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

您应该PDOStatement::fetch()用于获取行。它以数字和关联方式获取（默认情况下）。你也可以改变它。

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.

手册参考。