PHP PDO准备的语句IN()数组 [英] PHP PDO prepared statement IN() array

问题描述

来自 PHP手册:

例如，不能在SQL语句的IN()子句中将多个值绑定到单个命名参数.

You cannot bind multiple values to a single named parameter in, for example, the IN() clause of an SQL statement.

但是可以使用问号参数标记使用多个值吗?还是每次数值数量更改时都必须prepare()一个新语句?

But can you use multiple values using question mark parameter markers? Or do you have to prepare() a new statement every time the number of values changes?

SELECT * FROM `table` WHERE `column` IN(?)

如果允许的话，您如何使它工作?

If this is allowed how do you make it work?

前面提到的两个问题都是关于 named 变量的，手册说这些变量不能绑定到多个参数.我在问问号(匿名)变量.

The two previous questions indicated were both about named variables, which the manual says can't be bound to multiple parameters. I was asking about question mark (anonymous) variables.

推荐答案

以下是我摘录的一些代码，以模拟所需的结果:

Here's some code I whipped up to simulate the desired outcome:

<?php
function preprepare(&$query, &$data) {
    preg_match_all('/\?/', $query, $matches, PREG_OFFSET_CAPTURE);
    $num = count($matches[0]);
    for ($i = $num; $i;) {
        --$i;
        if (array_key_exists($i, $data) && is_array($data[$i])) {
            $query = substr_replace($query, implode(',', array_fill(0, count($data[$i]), '?')), $matches[0][$i][1], strlen($matches[0][$i][0]));
            array_splice($data, $i, 1, $data[$i]);
        }
    }
}

$query = 'SELECT * FROM `table` WHERE `col1` = ? AND `col2` IN(?) AND `col3` = ? AND `col4` IN(?)';
$data = array('foo', array(1, 2, 3), 'bar', array(4, 2));
preprepare($query, $data);
var_dump($query, $data);
?>

它输出:

array (size=2)
  0 => string 'SELECT * FROM `table` WHERE `col1` = ? AND `col2` IN(?,?,?) AND `col3` = ? AND `col4` IN(?,?)' (length=93)
  1 => 
    array (size=7)
      0 => string 'foo' (length=3)
      1 => int 1
      2 => int 2
      3 => int 3
      4 => string 'bar' (length=3)
      5 => int 4
      6 => int 2

然后可以将查询和数据用于常规的PDO准备的语句中.我不知道它是否能解决所有PDO的魔术问题，但到目前为止效果还不错.

The query and data can then be used in a normal PDO prepared statement. I don't know if it accounts for all PDO magic niceness but it works pretty well so far.

这篇关于PHP PDO准备的语句IN()数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！