PHP prepared statement with dynamic WHERE clause using IN(…) clause

Question

I'm facing a problem with IN() clause in a PHP script, i receive data from another page in a POST request as follow :

if( isset($_POST['mycountry'])){        
    if($_POST['mycountry']) $where[] = 'pays in ( :pays )';
};

$_POST['mycountry'] : contain the countries selected by the user (user can select zero or many countries)

If the user select country/countries, i add the concerned column and the values(countries) to the IN clause.

if(isset($_POST['mycountry'])){ 
$tags = implode(', ', $_POST['mycountry']);
$stmt->bindParam(':pays', $tags, PDO::PARAM_BOOL);  
}

When the user select zero or only one country it work fine, once the user choose more than one it return nothing!!

Any idea ?

PDO parameters can not bind arbitrary strings to construct SQL - they offer 1:1 replacements for variable values in a prepared statement. So, passing the single placeholder `:pays` results in the single string value of all your comma-separated tags together being sent into the query. There are a few alternative solutions proposed in the linked question. — Michael Berkowski, Jan 26 '14 at 22:16
The problem that work for one selected item and not when the user select more than one!! — MDIT, Jan 26 '14 at 22:16
Right. Because when only one is selected, the query MySQL ultimately sees is `WHERE pays IN ('1')` --that is valid and returns results. But when multiples are selected, MySQL sees `WHERE pays IN ('1,2,3,4,5')` Note, that is _one single string value with numbers and commas_, not a comma-separated list of values. The query is not syntactically invalid, but will never match your rows. You can't do what you're attempting as written. See the high-scoring answers in the linked question. — Michael Berkowski, Jan 26 '14 at 22:21
As you said WHERE pays IN ('1,2,3,4,5') but why PDO add ' ' inside the IN clause ? why not WHERE pays IN (1,2,3,4,5) ? — MDIT, Jan 26 '14 at 22:29
PDO does not literally quote it that way unless emulate prepares is enabled, but the point is it passes it as a single string parameter to a MYSQL prepared statement. As mentioned, placeholders are not for constructing SQL dynamically. They point to where single values are to be insertes — Michael Berkowski, Jan 26 '14 at 22:37

score 0 · Answer 1 · answered Jan 26 '14 at 22:36

Well I do know that In oracle you cannot bind an array to a single var. I think this is the same for PDO.

What you will need to do is to run a for each loop and create a one to one binding and replace the original bind with the new one to one binding that you generated. (and updating of course the bind array to have key - value pairs that you generated)

PHP prepared statement with dynamic WHERE clause using IN(…) clause

1 Answers1