Using prepared statements in PHP with the IN function in SQL

Question

I'm working on a tagging system using 3 tables (normalised). I wanted to create a prepared statement for when some searches for example "red apples", that it brings up all the items which have been tagged "red" and "apples".

Currently my query looks something like this:

$stmt = $db->prepare("SELECT co.content_id, co.description FROM em_content AS co LEFT JOIN em_contenttags AS ct ON co.content_id = ct.content_id LEFT JOIN em_tags AS ta ON ct.tag_id = ta.tag_id WHERE ta.tag IN (?)");
$stmt->bind_param("s", $query);
$stmt->execute();
$stmt->store_result();

I've tried making $query an array and using placeholders for the "?" in the query and "s" in the bind_param variable, but I can't pass the $query as an array otherwise it throws an error.

Is there any way to make this work with prepared statements?

Just FYI, I'm not using PDO, I'm using mysqli

I know that, but I want to be able to select a random number of tags, so for example I search for "red apples", you might search for "green apples from new york". The length changes each time, but bind_param doesn't allow for arrays nor can I dynamically give it a random assortment of variables each time. That's the problem I'm having, so I can't send it as a string either — allanminium
– allanminium, Commented Feb 22, 2012 at 22:59

deceze · Accepted Answer · 2012-02-22 00:13:33Z

1

You'll have to build up the placeholders dynamically, so you'll have one ? for each element of the array:

$array = array('foo', 'bar', 'baz');
$placeholders = join(',', array_fill(0, count($array), '?'));
$stmt = $db->prepare('SELECT ... WHERE IN (' . $placeholders . ')');
$stmt->execute($array);

Not 100% sure how to use MySQLi's bind mechanism here, the above works for PDO.

answered Feb 22, 2012 at 0:13

deceze♦

525k89 gold badges806 silver badges954 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

allanminium Over a year ago

Yea I've seen something similar on stackoverflow somewhere, but it's all for PDO and not for mysqli. I don't want to have to change to PDO this far into the project either :( sigh might have to go down the mysqL_escape_string route

Your Common Sense Over a year ago

just add str_repeat() call to create something like "sss" string to use with mysqli bind

allanminium Over a year ago

but then how do I add in the variables? In this case, the $query variable which contains an unknown number of tags. I can't do $stmt->execute($array); as mysqli doesn't allow for any variables to be passed in the execute function

El Barto · Accepted Answer · 2012-02-22 00:05:26Z

0

As far as I know, you can't use placeholders inside "IN()". You need to concatenate that part of the query.

answered Feb 22, 2012 at 0:05

El Barto

9291 gold badge5 silver badges18 bronze badges

3 Comments

allanminium Over a year ago

I've got a workaround for getting placeholders for the IN part, but I can't seem to pass $query in "$stmt->bind_param("s", $query);" as an array which I thought was possible :( how do you concatenate that part of the query?

El Barto Over a year ago

I meant something like this: $sql = "SELECT * FROM your_table WHERE id IN (" . implode(",", $idList) . ")"; But you should check out the solution that was suggested by @deceze would be better if you can get it to work with mysqli_*

El Barto Over a year ago

It would be a prepared statement but not that useful since you wouldn't be binding variables to it.

Collectives™ on Stack Overflow

Using prepared statements in PHP with the IN function in SQL

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related