問題描述
我正在修改一些 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查詢的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！