PHP PDO使用IN和WHERE运算符准备语句

Question

I am new to PDO (coming from mysqli) and I have been searching for hours and cannot seem to figure out how to combine the WHERE and IN operators with PDO prepared statements.

Simple 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 Statement (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);

Combining (Not Working):

$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);

Thanks In Advance!

Answer 1

I believe the reason this is not working is because you are using both

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

To use an array and make it work do as follows:

prepare($sql);

execute($myArr);

In $myArr you should include whatever columns = ? is supposed to be, you can add this to the beginning of the array using array_unshift() like this array_unshift($myArr, 'X'); http://php.net/manual/en/function.array-unshift.php

After calling array_unshift() the variable $myArr will have the values as follows:

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

Now you can call the PDO statements correctly:

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