带有 IN(?) 的 mySQL bind_param [英] mySQL bind_param with IN(?)

问题描述

在我的所有查询中使用 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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！