sql prepared statment don't work with WHERE IN clause

Question

So I have this sql preparted statment:

SELECT carName, modelName 
FROM cars 
INNER JOIN model ON cars.idCar = model.idCar 
WHERE carName IN (?)`

$input = " 'toyota','honda' "

I am trying to put this into the sql statment, but it gives zero rows out. I have tried the query in phpMyAdmin and there it works all fine. Anyone know the problem?

If you're not getting user input, why use a prepared statement for? — Funk Forty Niner, Feb 21 '20 at 21:36
Careful @FunkFortyNiner ... some regulars around here think you should use prepared statements for everything. Even when there is nothing input in the sql at all! lol — IncredibleHat, Feb 21 '20 at 21:39
@Hansen well, you have a few answers below, have a go at those. — Funk Forty Niner, Feb 21 '20 at 21:41

score 1 · Answer 1 · answered Feb 21 '20 at 21:28

You need one parameter per value in the IN list. If you pass a single parameter, it is interpreted as a unique string that contains a comma, which is not what you want (and you end up with no match, since none of the names in the table matches this value).

So for two parameters:

SELECT carName, modelName 
FROM cars 
INNER JOIN model ON cars.idCar = model.idCar WHERE carName IN (?, ?);

Sherif · Answer 2 · 2020-02-21T21:44:17.313

The IN clause takes multiple arguments and each parameter can only take on one value. So to automate the process of inserting multiple parameters in this statement you could use something like the following:

/* Execute a prepared statement using an array of values for an IN clause */
$params = ['toyota', 'honda'];
/* Create a string for the parameter placeholders filled to the number of params */
$place_holders = implode(',', array_fill(0, count($params), '?'));

/*
    This prepares the statement with enough unnamed placeholders for every value
    in our $params array. The values of the $params array are then bound to the
    placeholders in the prepared statement when the statement is executed.
    This is not the same thing as using PDOStatement::bindParam() since this
    requires a reference to the variable. PDOStatement::execute() only binds
    by value instead.
*/
$st = $db->prepare("SELECT carName, modelName FROM cars WHERE carName IN ($place_holders)");
$st->execute($params);

sql prepared statment don't work with WHERE IN clause

2 Answers2