Trying to figure out PHP PDO prepared statements and placeholders. Placeholder in "IN" clause of MySQL statement

Question

I'm new to PDO and prepared statements. Why does this work:

$sth = $dbh->prepare("SELECT * 
                      FROM skyrim_ingredients 
                      WHERE ing_name 
                      IN ('blue butterfly wing', 'blue dartwing', 'blue mountain flower');");
while($row = $sth->fetch()) {
    echo $row['ing_id'];
}

...but this doesn't:

$ing_string = 'blue butterfly wing', 'blue dartwing', 'blue mountain flower';
$sth = $dbh->prepare("SELECT * 
                      FROM skyrim_ingredients 
                      WHERE ing_name 
                      IN (?);");
$sth->bindParam(1, $ing_string);
$sth->execute();
while($row = $sth->fetch()) {
    echo $row['ing_id'];
}

I read that you cant use parameters for tables or columns, is this the case with IN clause, too?

You should refer to this answer, it explains your exact problem, http://stackoverflow.com/questions/920353/php-pdo-can-i-bind-an-array-to-an-in-condition tho you would need to change `$ing_string` to an array — Lawrence Cherone, Apr 19 '12 at 04:30

belwood · Answer 1 · 2012-04-19T04:36:06.070

1

Parameter substitution using ? is not the same as variable expansion -- which is what it looks like you're expecting in the case that doesn't work. The actual SQL that gets to the db might look like ... IN ("'blue butterfly wing', 'blue dartwing', 'blue mountain flower'"), so of course it wouldn't work.

edited Apr 19 '12 at 04:36

answered Apr 19 '12 at 04:25

belwood

3,320
11
38
45

I was thinking string substitution. I didn't realize I would need a separate placeholder for each value, not just the whole string. – pdizz Apr 19 '12 at 14:32

Trying to figure out PHP PDO prepared statements and placeholders. Placeholder in "IN" clause of MySQL statement

1 Answers1