問題描述

在我的所有查詢中使用 bind_param，我現在想使用 IN(?)，其中列表中的元素數量可以變化.

Using bind_param on all my queries, I now want to use an IN(?) where the number of elements in the list can vary.

我在這里使用的 SQLout 函數基本上做了一個 $sql_db->prepare、->bind_param、->execute(), ->store_result(), ->bind_result

The SQLout function I'm using here basically does a $sql_db->prepare, ->bind_param, ->execute(), ->store_result(), ->bind_result

// the code below does not work as the query only matches on element 'a':
$locations = ('a','b','c','d','e');

SQLout ("SELECT Name FROM Users WHERE Locations IN (?)",
    array('s', $locations), array(&$usrName));


// the code below does work as a brute-force method,
// but is not a viable solution as I can't anticipate the number of elements in $locations going forward:

SQLout ("SELECT Name FROM Users WHERE Locations IN (?,?,?,?,?)",
    array('sssss', $locations[0],$locations[1],$locations[2],$locations[3],$locations[4]), array(&$usrName));

有沒有人想出一個更優雅的解決方案?

Has anyone come up with a more elegant solution to this?

推薦答案

這是一個占位符落在他們臉上的地方.減去自動轉義，它們幾乎實際上只是內部的字符串替換操作，這意味著如果您有 WHERE Locations IN (?)，并傳入 1,2,3,4，你會得到等價的

This is one place placeholders fall on their faces. Minus the auto-escaping, they're almost literally just a string replacement operation internally, meaning that if you have WHERE Locations IN (?), and pass in 1,2,3,4, you'll get the equivalent of

WHERE Locations IN ('1,2,3,4')  // note, it's a string, not individual comma-separated integers

邏輯上等價于

WHERE Locations = '1,2,3,4' // again, just a string

而不是預期

WHERE Locations = 1 OR Locations = 2 OR Locations = 3 OR Locations = 4

唯一實用的解決方案是構建您自己的逗號分隔占位符列表 (?)，例如:

The only practical solution is to build your own list of comma-separated placeholders (?), e.g:

$placeholders = implode(',', array_fill(0, count($values), '?'));
$sql = "SELECT Name FROM Users WHERE Locations IN ($placeholders)";

然后綁定你的參數就正常了.

and then bind your parameters are usual.

這篇關于帶有 IN(?) 的 mySQL bind_param的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！