dongnianchou7047
2016-08-10 00:48
采纳率: 0%
浏览 30
已采纳

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

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!

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • dtrotfd1012 2016-08-10 01:09
    已采纳

    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);
    
    打赏 评论

相关推荐 更多相似问题