带有IN和WHERE操作符的PHP PDO预处理语句 - PHP PDO Prepared Statements with IN and WHERE Operators

PHP PDO Prepared Statements with IN and WHERE Operators

我是PDO的新手(来自mysqli)，我已经搜索了几个小时，似乎无法弄清楚如何将WHERE和IN操作符与PDO准备的语句结合起来。

简单WHERE (Works Fine):

$value1 = 'val1';
$value2 = 'val2';
$stmt = $pdo->prepare('SELECT * FROM mytable WHERE val1 = ? AND val2 = ?');
$stmt->bindParam(1, $value1);
$stmt->bindParam(2, $value2);
$stmt->execute();

IN语句(Works Fine)

$myArr = ['NY', 'PARIS', 'ROME'];
$q  = str_repeat('?,', count($myArr) - 1) . '?';
$sql = "SELECT * FROM myTable WHERE cities IN ($q)";
$stmt = $db->prepare($sql);
$stmt->execute($myArr);

合并(无效):

$value1 = 'val1';
$myArr = ['NY', 'PARIS', 'ROME'];
$q  = str_repeat('?,', count($myArr) - 1) . '?';
$sql = "SELECT * FROM myTable WHERE column = ? AND cities IN ($q)";
$stmt = $db->prepare($sql);
$stmt->bindParam(1, $value1);
$stmt->execute($myArr);

提前感谢!

我认为这不起作用的原因是你同时使用了

$stmt->bindParam(1, $value1); $stmt->execute($myArr);

要使用数组并使其工作，请执行以下操作:

prepare($sql);

execute($myArr);

在$myArr中，您应该包括任何columns = ?应该是，您可以将其添加到使用array_unshift()的数组的开头，如array_unshift($myArr, 'X'); http://php.net/manual/en/function.array-unshift.php

调用array_unshift()之后，变量$myArr的值如下:

[myArr] => Array
        (
            [0] => X
            [1] => NY
            [2] => PARIS
            [3] => ROME
        )

现在你可以正确调用PDO语句了:

prepare($sql);
execute($myArr);