如何将 php 数组与 sql IN 运算符一起使用? [英] How to use php array with sql IN operator?

问题描述

我有两个值的数组，我想在选择查询中将它与 sql IN 运算符一起使用.

I have and array with two values and I want to use it with sql IN operator in select query.

这是我的表的结构

id comp_id
1   2
2   3
3   1

我有一个数组 $arr 它有两个值 Array ( [0] => 1 [1] => 2 )

I have an array $arr which have two values Array ( [0] => 1 [1] => 2 )

我想获取 comp_id 1 和 comp_id 2 的记录.所以我写了以下查询.

I want to fetch the record of comp_id 1 and comp_id 2. So I wrote the following query.

SELECT * from table Where comp_id IN ($arr)

但它不返回结果.

推荐答案

因为你有普通的整数，你可以简单地用逗号连接它们:

Since you have plain integers, you can simply join them with commas:

$sql = "SELECT * FROM table WHERE comp_id IN (" . implode(',', $arr) . ")";

如果使用字符串，尤其是不受信任的输入:

If working with with strings, particularly untrusted input:

$sql = "SELECT * FROM table WHERE comp_id IN ('" 
     . implode("','", array_map('mysql_real_escape_string', $arr)) 
     . "')";

注意这不会处理诸如 NULL 之类的值(将被保存为空字符串)，并且会在数值周围盲目添加引号，如果使用 严格的mysql模式.

Note this does not cope with values such as NULL (will be saved as empty string), and will add quotes blindly around numeric values, which does not work if using strict mysql mode.

mysql_real_escape_string 是原 mysql 驱动扩展的函数，如果使用较新的驱动，如 mysqli，使用 mysqli_real_escape_string 代替.

mysql_real_escape_string is the function from the original mysql driver extension, if using a more recent driver like mysqli, use mysqli_real_escape_string instead.

但是，如果您只想处理不受信任的数字，您可以使用 intval 或 floatval 来清理输入:

However, if you just want to work with untrusted numbers, you can use intval or floatval to sanitise the input:

$sql = "SELECT * FROM table WHERE comp_id IN (" . implode(",", array_map('intval', $arr)) . ")";

这篇关于如何将 php 数组与 sql IN 运算符一起使用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！