使用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屋！