How to deliver params for SELECT with IN in mysqli

Question

My prepared query looks like this:

   $sql = "SELECT $t1.id FROM $t1 WHERE $t1.name IN (?)";

When I try:

   $stmt = Sql::$select->prepare($sql);
   $string="'aaa','bbb','ccc'";
   $stmt->bind_param('s', $string);

...it doesn't return the expected results. It treats the string as one name and not many separate names. How can it be solved?

The rest of the function:

   $stmt->execute();
   $stmt->store_result();
   return $stmt;

i have no prior experience in prepared statements, but i've experienced this problem while using stored procedures. Probably you need to bind each argument in the IN statement. — Quicksilver, Nov 12 '12 at 09:42
I think I understand what you mean @KhaleelAKarim, but i cant do it - the number of names in the string is unknown — Yair Budic, Nov 12 '12 at 09:47
what is `Sql`?, assuming that `Sql::$select` is your connection object, what is the message in `Sql::$select->error` (after the prepare)? — Andreas Wallner, Nov 12 '12 at 09:55
[duplicate?](http://stackoverflow.com/questions/1534377/php-mysqli-where-in) — StasGrin, Nov 12 '12 at 10:11
well, it dosent any more returns error -but the work is still not being done - it takes the whole string as one name. when i put the string hard coded - it works as expceted. I think i will just do so, and avoid the bind. — Yair Budic, Nov 12 '12 at 10:18
There is a [similar question](http://stackoverflow.com/questions/178479/preparedstatement-in-clause-alternatives) available, Please see the answers. — Quicksilver, Nov 12 '12 at 10:34

Quicksilver · Answer 1 · 2012-11-12T10:30:36.977

0

Try altering query like this

$sql = "SELECT $t1.id FROM $t1 WHERE FIND_IN_SET($t1.name, ?)>0";
$stmt = Sql::$select->prepare($sql);
$string='aaa,bbb,ccc';
$stmt->bind_param('s', $string);

And this solution is not reliable. Please see FIND_IN_SET(str,strlist)

Correct method is to use separate placeholders for each element in the IN statement.

Another suggestion, get rid of the IN statement and run through a loop in php to generate the query and bind the params.

edited Nov 12 '12 at 10:30

answered Nov 12 '12 at 10:10

Quicksilver

2,546
3
23
37

it doesnt bring the result (still treat the string as one long name..) – Yair Budic Nov 12 '12 at 10:39

score -2 · Answer 2 · edited Nov 12 '12 at 10:23

The problem is that the bind_param function with 's' threats the parameter as a single string, so it is basically transforming your query to:

"SELECT $t1.id FROM $t1 WHERE $t1.name IN (''aaa','bbb','ccc'')";

A quick fix would be to change the string variable to:

$string="aaa','bbb','ccc";

Note: without the leading/trailing quotes (you can make this using trim($string, "'")

You may also try inverting the quotes: using " instead of ' if this does not work.

A more reliable and robust solution is to change the query to incude a ? for each string (using arrays with expolode() / implode() php functions.

Sorry but you've seriously misunderstood how prepared statements work. — Álvaro González, Nov 12 '12 at 10:23

How to deliver params for SELECT with IN in mysqli

2 Answers2