0

I have a couple of situations where I have an array of input, such as:

$myArray = array( "apple", "banana", "orange", "pear" );

Where the array could have any number of fruits in it. I want to use MySQL prepared statements in PHP to create a query similar to:

SELECT * FROM fruitcart WHERE fruitname IN ('apple','banana','orange','pear');

Previously, I was accomplishing this by doing something like:

$query = "SELECT * FROM fruitcart WHERE fruitname IN ('" . implode( "','", $myArray ) . "')";

but I'd like to know if there is a way I could do something similar with prepared statements?

Dave Smith
  • 13
  • 2
  • dup of [MySQL Prepared statements with a variable size variable list](http://stackoverflow.com/q/327274/), [Parameterizing an SQL IN clause?](http://stackoverflow.com/q/337704/). – outis Jul 19 '12 at 06:54

3 Answers3

1

There is no way to do that with a prepared statement. The only possibility is to do something like this:

$query = "SELECT * FROM fruitcart WHERE fruitname = ? OR fruitname = ? OR fruitname = ? ...

You can easily build a statement like this with an foreach loop.

But be aware that, since your array will probably have different amounts of values, this might cause some confusion in the database optimizer algorithms. For optimal performance you might want to prepare statements with for example 128, 64, 32, 16, 8, 4, 2, 1 slots and then use the biggest one you can fill until you got all your values from the database. That way the optimizer is able to deal with a much more limited amount of statement skeletons.

You can also use a temporary table for this. For example create a table that only contains the values (apple, banana, ...) and an id for your value set.

You can then insert the array of values into the database using a unique set-id (php offers a guid function for example) and then selecting them in a subquery:

$query = "SELECT * FROM fruitcart WHERE fruitname IN (SELECT fruitname FROM temptable WHERE setid = ?)"

That's easily preparable and will perform quite good. You can use an in-memory table for the temptable so it will be very fast.

bardiir
  • 14,556
  • 9
  • 41
  • 66
0
$placeholders = rtrim(str_repeat('?, ', count($myArray)), ', ') ;
$query = "SELECT * FROM fruitcart WHERE fruitname IN ($placeholders)";

$stm = $db->prepare($query) ;
$stm->execute($myArray) ;
sel
  • 4,982
  • 1
  • 16
  • 22
-2

You are using the proper way to do that, you can edit

$list = implode( ',', $myArray );
$query = "SELECT * FROM fruitcart WHERE fruitname IN ('" . $list . "')";

That's it :)

Hiren Soni
  • 574
  • 3
  • 11