MySQL PDO如何为IN()绑定参数 [英] MySQL PDO how to bind params for IN()

问题描述

我正在使用PHP，MySQL和PDO.

I'm using PHP, MySQL and PDO.

$ids = '1,2,3';

我有一个这样的查询有效:

I have a query like this which works:

SELECT *
FROM table
WHERE id IN($ids)

这将按预期返回3行

如果我使用:

SELECT *
FROM table 
WHERE id IN(:ids)

$stmt = $this->db->prepare($q);
$params = array('ids' => $ids);
$stmt->execute($params);

它仅返回1行.

如何绑定ID?

根据 https://phpdelusions.net/pdo 我应该使用:

$arr = [1,2,3];
$in  = str_repeat('?,', count($arr) - 1) . '?';
$sql = "SELECT * FROM table WHERE column IN ($in)";

但是如何将存储在$ ids中的我的1,2,3 id放入$ arr = [1,2,3]，因为如果我写

but how can I put my ids of 1,2,3 which are stored in $ids into $arr = [1,2,3] because if I write

 $arr = [$ids] 

基本上是写

 $arr = ['1,2,3']

代替

$arr = [1,2,3]

推荐答案

我知道了:

$ids = '1,2,3';

将$ ids字符串分解为一个数组:

Explode the $ids string into an array:

$ids_array = explode(',', $ids);

这给出了:

$ids_array[] = 1;
$ids_array[] = 2;
$ids_array[] = 3;

创建一个以逗号分隔的问号字符串.问号的数量与数组值的数量匹配

Create a comma-delimited string of question marks. The number of question marks matches the number of array values

$in  = str_repeat('?,', count($ids_array) - 1) . '?';

这将产生一个类似于以下内容的字符串:

This produces a string that looks like:

?,?,?

将该字符串放入sql

Put that string into the sql

$q = "SELECT *
    FROM table
    WHERE id IN($in) ";


$stmt = $this->db->prepare($q);

执行查询，将数组作为参数传递

Execute the query, passing the array as a parameter

$stmt->execute($ids_array);

这篇关于MySQL PDO如何为IN()绑定参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！