开发者

PDO with "WHERE... IN" queries [duplicate]

This question already has answers here: Can I bind an array to an IN() condition in a PDO query? (23 answers) Closed last year.

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 wi开发者_StackOverflow社区ll 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));

When I do this, only the first ID is deleted. (I assume it throws out the comma and everything after it.)

I've also tried making $idlist an array, but then it doesn't delete anything.

What's the proper way to use a list of items in a PDO prepared statement?


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

and loop to bind the parameters.


This may be helpful too:

https://phpdelusions.net/pdo#in

$arr = [1,2,3];
$in  = str_repeat('?,', count($arr) - 1) . '?';
$sql = "SELECT * FROM table WHERE column IN ($in)";
$stm = $db->prepare($sql);
$stm->execute($arr);
$data = $stm->fetchAll();


I would make $idlist and array, then simply loop through the array using foreach to delete the specific item.

$idlist = array('260','201','221','216','217','169','210','212','213');

$stmt = $dbh->prepare("DELETE FROM `foo` WHERE `id` = ?");
$stmt->bindParam(1, $id);

foreach ($idlist as $item){
    $id = $item;
    $stmt->execute();
}
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜