问题描述
我正在修改一些 PHP 代码以使用 PDO 进行数据库访问，但我遇到了WHERE... IN"查询的问题.
I'm reworking some PHP code to use PDO for the database access, but I'm running into a problem with a "WHERE... IN" query.
我正在尝试从数据库中删除一些内容，基于表单上的哪些项目被检查.列表的长度和内容会有所不同，但对于这个例子，想象一下:
I'm trying to delete some things from a database, based on which items on a form are checked. The length and content of the list will vary, but for this example, imagine that it's this:
$idlist = '260,201,221,216,217,169,210,212,213';
然后查询看起来像这样:
Then the query looks like this:
$query = "DELETE from `foo` WHERE `id` IN (:idlist)";
$st = $db->prepare($query);
$st->execute(array(':idlist' => $idlist));
当我这样做时，只会删除第一个 ID.(我假设它会抛出逗号及其后的所有内容.)
When I do this, only the first ID is deleted. (I assume it throws out the comma and everything after it.)
我也试过把 $idlist 变成一个数组，但它并没有删除任何东西.
I've also tried making $idlist an array, but then it doesn't delete anything.
在 PDO 准备好的语句中使用项目列表的正确方法是什么?
推荐答案
由于您无法将值(数字)与控制流逻辑(逗号)与准备好的语句混合使用，因此每个值需要一个占位符.
Since you can't mix Values (the Numbers) with control flow logic (the commas) with prepared statements you need one placeholder per Value.
$idlist = array('260','201','221','216','217','169','210','212','213');
$questionmarks = str_repeat("?,", count($idlist)-1) . "?";
$stmt = $dbh->prepare("DELETE FROM `foo` WHERE `id` IN ($questionmarks)");
并循环绑定参数.
这篇关于带有“WHERE ... IN"的 PDO查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！