在哪里使用IN [英] Using an IN in a where

问题描述

使用MySQLi在where子句中使用IN时遇到了一些麻烦，这是我的查询:

I've been having some trouble using an IN in a where clause using MySQLi this is my query:

SELECT * FROM core_tags WHERE tag_id IN (1,2,3,4,5) GROUP BY tag_id ORDER BY tag_popularity ASC

如果我在PHP My Admin中运行它，那么我会得到5个结果，正如我所期望的那样.但是，如果我使用以下代码在PHP中运行它，则只会得到tag_id'1'的一个结果.

If I run this in PHP My Admin then I get 5 results as I would expect. However if I run it in PHP with the following code I only get one result of the tag_id '1'.

这是我的PHP.最初，我是使用类中的函数运行它的，但是我已经对其进行了手工编码，以测试它不仅仅是函数中具有相同问题的错误.

Here's my PHP. Originally I was running it using functions in a class but I've hand coded it to test that it wasn't simply an error in my functions with the same problem.

$mysqli = new mysqli(DB_SERVER, DB_NAME, DB_PASSWORD, DB_NAME);
$rawQuery = 'SELECT * FROM core_tags WHERE tag_id IN (?) GROUP BY tag_id ORDER BY tag_popularity ASC';
$stmt = $mysqli->prepare($rawQuery);
$stmt->bind_param("s", $tag_ids);
$tag_ids = "1,2,3,4,5";
$stmt->execute();
$stmt->bind_result($tag_id, $tag_name, $tag_desc, $tag_popularity);

while ($stmt->fetch()) {
    printf ("%s\n", $tag_name);
}

$stmt->close();
die();

任何人都知道为什么mysqli版本只返回一行吗?与MySQL My Admin一样，使用MySQL代替mysqli也可以正常工作.

Anyone have any idea why the mysqli version only returns one row? Using MySQL instead of mysqli works fine as well, same as PHP My Admin.

推荐答案

使用字符串准备好的语句将使您的最终SQL看起来像:

Using a string prepared statement will cause your final SQL to look like:

IN ('1,2,3,4,5')

带引号，这不是您想要的.我要做的是这样:

with the quotes, which is not what you want. What I'd do is this:

$ids= array(1,2,3,4,5);
$mysqli = new mysqli(DB_SERVER, DB_NAME, DB_PASSWORD, DB_NAME);

$rawQuery = 'SELECT * FROM core_tags WHERE tag_id IN (';
$rawQuery .= implode(',',array_fill(0,count($ids),'?'));
$rawQuery .= ') GROUP BY tag_id ORDER BY tag_popularity ASC';
$stmt = $mysqli->prepare($rawQuery);
call_user_func_array(array($stmt,'bind_param'),$ids);
$stmt->execute();
$stmt->bind_result($tag_id, $tag_name, $tag_desc, $tag_popularity);

while ($stmt->fetch()) {
    printf ("%s\n", $tag_name);
}

如果implode array_fill令人困惑，这只是一种创建与$ids相同大小且充满"?"的数组，然后将其转换为csv的简便方法.

If the implode array_fill is confusing, it just is a shorthand way of creating an array of the same size as $ids full of "?", then turning them to a csv.

更新:非绑定参数方式

当然，如果您想跳过无用的绑定参数，并且可以信任已经清理过的$ids列表，则可以改为执行此操作，并跳过bind_params部分:

Of course, if you want to skip the bind params nonsense, and you can trust the list of $ids to already be sanitized, you can just do this instead, and skip the bind_params section:

$rawQuery = 'SELECT * FROM core_tags WHERE tag_id IN (';
$rawQuery .= implode(',',$ids);
$rawQuery .= ') GROUP BY tag_id ORDER BY tag_popularity ASC';

如果您不信任数据:

function clean_ids(&$item){
 $item = intval($item);
}

$clean_ids = array_walk($ids,'clean_ids');
$rawQuery = 'SELECT * FROM core_tags WHERE tag_id IN (';
$rawQuery .= implode(',',$clean_ids);
$rawQuery .= ') GROUP BY tag_id ORDER BY tag_popularity ASC';

这篇关于在哪里使用IN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！