Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

i have a query like this with an IN clause

sql = "

SOME QUERY WHERE some_item IN ()

";

Do i have to do a for loop to to put all items of the php array into the IN clause or is there a function or method that does this?

regards, alex

share|improve this question
Note you can format lines as code by indenting them four spaces. The "{}" button in the editor toolbar does this for you. Edit your question and try it out. Click the orange question mark in the editor toolbar for more information and tips on formatting. – outis Apr 23 '11 at 20:04
add comment (requires an account with 50 reputation)

5 Answers

up vote 0 down vote accepted

You could try implode

$item_ids = array( 1, 2, 3, 4, 5); // somehow populate this array
$item_list = implode(',', $item_ids);

$sql = "SOME QUERY WHERE some_item IN ({$item_list})";
share|improve this answer
add comment (requires an account with 50 reputation)
up vote 1 down vote

http://php.net/manual/en/function.implode.php

here is a quick example :
$array = array('lastname', 'email', 'phone');
$comma_separated = implode(",", $array);

share|improve this answer
add comment (requires an account with 50 reputation)
up vote 1 down vote

For numeric values in an array:

sprintf("... WHERE some_item IN (%s) ...",implode(",",$numeric_array));

For string values:

sprintf("... WHERE some_item IN ('%s') ...",implode("','",$escaped_string_array));

Be careful to escape the values when building the arrays.

Hope this helps!

share|improve this answer
1  
you could do an array_filter() before with a call to escape method etc. php.net/manual/en/function.array-filter.php – dogmatic69 Feb 8 '11 at 11:07
+1 for useful comment, thanks :) – aorcsik Feb 8 '11 at 11:09
add comment (requires an account with 50 reputation)
up vote 1 down vote

If you have the list of things in an array, you can use JOIN to build it for you. It's easiest for numeric values, since you don't have to put quotes around things. Something like this:

$sql = 'SOME QUERY WHERE some_item IN (' . join(',', $a_myData) . ')';

If your array contains strings, you should escape that data as you put the data into the array. But once you've done that, you can do something similar to above, adding quote marks.

$sql = 'SOME QUERY WHERE some_item IN ("' . join('","', $a_myData) . '")';

One potential issue is an empty array, since the above example would give you IN (""), which could potentially match something you don't want.

The above example also doesn't account for NULL values. One way around that is to populate your array with the strings with quotes already around them (so you can have NULL as its own value).

share|improve this answer
thanks you so much! – Alexander Feb 8 '11 at 9:49
add comment (requires an account with 50 reputation)
up vote 0 down vote

You can use implode, like:

$ids = array(1, 2, 3);
$query = 'SELECT * FROM items WHERE id IN(' . implode(',', $ids) . ')';
share|improve this answer
thanks you so much! – Alexander Feb 8 '11 at 9:48
add comment (requires an account with 50 reputation)

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.