将 PHP while 循环转换为使用 PDO

2021-12-26 00:00:00 sql php pdo

我目前正在通过切换到 PDO 来更新我的应用.我有以下代码:

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();

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

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;
}

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

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?

------------更新 1---------------------------------------------------

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

我尝试了以下代码:

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

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

Which returns: Invalid argument supplied for foreach() error

推荐答案

PHP 手册中的标准文档通常很有帮助.PHP手册中有一个用PDO执行for循环的例子，PDO详情.

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'] . "
";
    }
}

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

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'] . "
";
    }
}

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

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

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

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.

在功能上，foreach 和 while 实现是等效的.从概念上讲，foreach 更合适，因为 while 循环具有在静态条件成立时循环的含义，而 foreach 循环遍历 a 的元素收藏.阅读一段时间之间的差异PHP 中的循环和 for 循环?" 部分故事.

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.

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

相关文章