php - mySQL bind_param 与 IN(?)

Question

这个问题已经有答案了:

How can I bind an array of strings with a mysqli prepared statement? (7 个回答)

已关闭 2 年前。

使用bind_param对于我的所有查询，我现在想使用 IN(?)其中列表中的元素数量可以变化。

我在这里使用的 SQLout 函数基本上执行 $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));

有没有人想出一个更优雅的解决方案？

Answer 1

这是占位符落在他们脸上的地方。减去自动转义，它们几乎实际上只是内部的字符串替换操作，这意味着如果您有 WHERE Locations IN (?)，并传入 1,2,3,4 ，您将得到相当于

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

唯一实用的解决方案是构建您自己的逗号分隔占位符列表 (?)，例如:

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

然后绑定(bind)你的参数是常见的。