PHP PDO for NOT IN query in MYSQL

Question

I have a post variable called $_POST['excludeids'] with the following value:

1,2,3,4,5,6,7,8,9

I want to pass this into an SQL query through NOT IN so I use the following query:

$STH = $DBH->prepare("SELECT * FROM books WHERE id NOT IN (:excludeids)");
$STH->bindValue(':excludeids', $_POST['excludeids']);
$STH->execute();

Binding the variable doesn't work in this context I don't know why. What's wrong with the above query?

see this question. http://stackoverflow.com/questions/920353/php-pdo-can-i-bind-an-array-to-an-in-condition — DevZer0, Jul 19 '13 at 13:03

score 9 · Accepted Answer · answered Jul 19 '13 at 13:06

9

It doesn't work in this way because an IN() clause expects a collection of values, not a comma separated string, which is what you are providing by attempting to bind them all as a single argument.

In order to make this work you will need to bind each element in the collection individually:

// Split the IDs into an array
$ids = preg_split('/\s*,\s*/', $_POST['excludeids'], -1, PREG_SPLIT_NO_EMPTY);

// Create an array of ? characters the same length as the number of IDs and join
// it together with commas, so it can be used in the query string
$placeHolders = implode(', ', array_fill(0, count($ids), '?'));

// Prepare the statement
$STH = $DBH->prepare("SELECT * FROM books WHERE id NOT IN ($placeHolders)");

// Iterate the IDs and bind them
// Remember ? placeholders are 1-indexed!
foreach ($ids as $index => $value) {
    $STH->bindValue($index + 1, $value, PDO::PARAM_INT);
}

// This should now work
$STH->execute();

answered Jul 19 '13 at 13:06

DaveRandom

87,921
11
154
174

Now I'm facing another problem...I want to bind another variable using `$STH->bindValue(':test', $_POST['test']);` but I'm getting the following error: `Invalid parameter number: mixed named and positional parameters` – Michael Samuel Jul 19 '13 at 13:45
@MichaelSamuel You will need to use question mark-style placeholders throughout the query in order for the above code to work - but you can easily at more at the appropriate place. If your extra param needs to go before the `IN()` clause, simply `bindValue(1, 'Your value')` before the loop and change `$index + 1` to `$index + 2`. – DaveRandom Jul 19 '13 at 13:53
Ok solved that but having another problem...why do i have to do the preg_split? When I tried to replace the first line in your code with `$ids = array($_POST['excldeids'])` the query returns nothing...I tried to send the string in jquery as `'1','2','3'` instead of `1,2,3` but still it returns nothing – Michael Samuel Jul 19 '13 at 14:01
1

Because `$_POST['excldeids']` is a comma separated string. You need it to be an array, with each comma-separated value as an element. `array($_POST['excldeids'])` would simply create an array with one element, your original comma-separated string. – DaveRandom Jul 19 '13 at 14:07
If I push the ids as an array using javascript...is there a way to use it directly in PHP instead of having to preg_split? – Michael Samuel Jul 19 '13 at 14:30
@MichaelSamuel If you are using jQuery, it provides a helper function called [`jQuery.serializeArray()`](http://api.jquery.com/serializeArray/) which will create a correctly formatted string for you to use directly in PHP. Alternatively you could use `JSON.stringify()` to pass the parameter as JSON, and then `json_decode()` it in PHP. But at the end of the day, the data is always being passed into a PHP as a string ans you will always need to decode it to an array - all the mechanisms are very similar. – DaveRandom Jul 19 '13 at 14:40
I found that using PHP explode function is faster and easier than the preg_split...thanks Dave very much for your effort :) – Michael Samuel Jul 19 '13 at 14:57
@MichaelSamuel As long your input string contains nothing but commas and digits, `explode()` will work fine. The only reason I went with `preg_split()` was to account for unwanted white space. – DaveRandom Jul 19 '13 at 15:05
[Explanation for using `preg_split` instead of `explode`](http://stackoverflow.com/a/64608/318765) – mgutt Apr 08 '15 at 23:13

score 3 · Answer 2 · answered Jul 19 '13 at 13:01

3

You will have to loop thru the ids (first exploding them into an array) and create new parameters dynamically, both in the SQL string and with bindValue.

answered Jul 19 '13 at 13:01

Daniel A. White

187,200
47
362
445

They are string, not array, I think – Royal Bg Jul 19 '13 at 13:01
i pass this as a string using jquery – Michael Samuel Jul 19 '13 at 13:02
can you show me an example because I can't understand you..thanks :) – Michael Samuel Jul 19 '13 at 13:03
i posted a question for your reference. look above – DevZer0 Jul 19 '13 at 13:04
Now I'm facing another problem...I want to bind another variable using `$STH->bindValue(':test', $_POST['test']);` but I'm getting the following error: `Invalid parameter number: mixed named and positional parameters` – Michael Samuel Jul 19 '13 at 13:31

score 0 · Answer 3 · answered Jul 17 '15 at 11:49

    $all_id = array(1, 2, 3, 4,5);
    $countArr = count($all_id);
    for($countArr; $countArr > 0; $countArr--)
        $in[]= '?';
    $in = implode(', ', $in);   
    $stmt = $dbh->prepare("
        SELECT ID
        FROM  b_iblock_element 
        WHERE  XML_ID NOT IN  ( ".$in.")
    ");
    if ($stmt->execute($all_id)) {
        while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            echo '<pre>'; print_r($row); echo'</pre>';
        }
    }
    $stmt->execute();

PHP PDO for NOT IN query in MYSQL

3 Answers3