带有 IN(?) 的 mySQL bind_param
在我的所有查询中使用 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.
相关文章