How to use php array with sql IN operator?

Question

I have and array with two values and I want to use it with sql IN operator in select query.

Here is the structure of my table

id comp_id
1   2
2   3
3   1

I have an array $arr which have two values Array ( [0] => 1 [1] => 2 )

I want to fetch the record of comp_id 1 and comp_id 2. So I wrote the following query.

SELECT * from table Where comp_id IN ($arr)

But it does not return the results.

Step 1 is to debug your own code. If you printed out the query you were sending to MySQL, you'd see that `SELECT * from table Where comp_id IN (Array)` is not right. Trying to concatenate an array to a string doesn't automatically turn it into a comma separated list of words. — Dan Grossman, Mar 08 '12 at 13:23
Step 2 is to search SO to find out how many thousands of times this question has already been answered — Mark Baker, Mar 08 '12 at 13:25
I answered on this problem, it will solve your problem too. http://stackoverflow.com/questions/920353/can-i-bind-an-array-to-an-in-condition/36070527#36070527 — 4EACH, Mar 17 '16 at 20:09

barryhunter · Accepted Answer · 2020-08-19T12:41:38.153

92

Since you have plain integers, you can simply join them with commas:

$sql = "SELECT * FROM table WHERE comp_id IN (" . implode(',', $arr) . ")";

If working with with strings, particularly untrusted input:

$sql = "SELECT * FROM table WHERE comp_id IN ('" 
     . implode("','", array_map('mysql_real_escape_string', $arr)) 
     . "')";

Note this does not cope with values such as NULL (will be saved as empty string), and will add quotes blindly around numeric values, which does not work if using strict mysql mode.

mysql_real_escape_string is the function from the original mysql driver extension, if using a more recent driver like mysqli, use mysqli_real_escape_string instead.

However, if you just want to work with untrusted numbers, you can use intval or floatval to sanitise the input:

$sql = "SELECT * FROM table WHERE comp_id IN (" . implode(",", array_map('intval', $arr)) . ")";

edited Aug 19 '20 at 12:41

answered Mar 08 '12 at 13:21

barryhunter

20,886
3
30
43

I used this method and it works fine but with a warning of `Warning: implode() [function.implode]: Invalid arguments passed in C:\wamp\www\x\x\x\xxx.php on line 81` – Ahmad Mar 08 '12 at 13:43
2

@Ahmad Your `$arr` is most likely not an array. Check that you are constructing it properly. – jprofitt Mar 08 '12 at 13:51
3

It will retrieve the same result but its not the best answer, you discovered to SQL Injection attacks, I answered on this problem, it will solve your problem too. http://stackoverflow.com/questions/920353/can-i-bind-an-array-to-an-in-condition/36070527#36070527 – 4EACH Mar 17 '16 at 20:09
3

This will only work for numeric values in your array. If `$arr` contains strings, they will be unquoted after the implode and the database engine will likely not be happy, e.g. `SELECT * FROM table WHERE id IN (foo, bar, baz);` is not valid SQL for most databases. – CXJ Dec 08 '17 at 15:23
Well the implication of the question was had numbers, although it wasnt absolutely explicit. – barryhunter Dec 08 '17 at 17:31
This should now be updated with mysqli_escape_string() I believe. – Dante Cullari Nov 14 '19 at 09:18
Well yes, if using MySQLi connector use mysqli functions, note its `mysqli_real_escape_string` but will need to pass the $link ident too! Not easy with array_map! So may be easier to use the object orientated style to as callable, `array_map(array($link,'escape_string'), $arr)` – barryhunter Nov 14 '19 at 13:27

score 16 · Answer 2 · answered Mar 08 '12 at 13:21

16

you need to convert the array into comma-separated string:

$condition = implode(', ', $arr);

And, additionally, you might want to escape the values first (if you are unsure about the input):

$condition = implode(', ', array_map('mysql_real_escape_string', $arr));

answered Mar 08 '12 at 13:21

Aurimas

2,518
18
23

1

Umm, you can't just call mysql_real_escape_string on the data, would still need to add quotes around the values (othrwise creating invalid SQL). – barryhunter Apr 11 '18 at 16:07

score 5 · Answer 3 · answered Mar 08 '12 at 13:21

5

$arr is a php array, to the sql server you need to send a string that will be parsed you need to turn your array in a list like 1, 2, etc..

to do this you can use the function http://php.net/implode

so before running the query try

$arr = implode ( ', ', $arr);

answered Mar 08 '12 at 13:21

mishu

5,347
1
21
39

I used this method and it works fine but with a warning of `Warning: implode() [function.implode]: Invalid arguments passed in C:\wamp\www\x\x\x\xxx.php on line 81` – Ahmad Mar 08 '12 at 13:43
@Ahmad that's strange.. maybe for a case where you don't have elements in that array or the variable is not initialized yet.. ? – mishu Mar 08 '12 at 13:52
See my comment above. This does not work for string values in the array. – CXJ Dec 08 '17 at 15:24
1

@CXJ sure, you are right, this answers only the case in the question posted by the OP... for strings you would have to quote *and* escape the values – mishu Dec 08 '17 at 15:35

score 5 · Answer 4 · answered Mar 08 '12 at 13:21

5

You need to implode your array with ',' comma

$imploded_arr = implode(',', $arr);

SELECT * from table Where comp_id IN ($imploded_arr)

answered Mar 08 '12 at 13:21

saba

382
6
14

score 4 · Answer 5 · answered Aug 16 '17 at 11:18

All the people here are proposing the same thing but i got a warning in WordPress because of a simple error. You need to add commas to your imploded string. To be precise something like this.

$query = "SELECT *FROM table Where comp_id IN ( '" . implode( "', '", $sanitized_brands ) . "' )";

Hoping it helps someone like me. :)

score 4 · Answer 6 · edited Mar 08 '12 at 13:49

4

you can only pass string to mysql as query, so try this

mysql_query("SELECT * FROM table WHERE comp_id IN (".implode(',',$arr).")");

edited Mar 08 '12 at 13:49

DaveRandom

87,921
11
154
174

answered Mar 08 '12 at 13:22

Chakradar Raju

2,691
2
26
42

score 3 · Answer 7 · answered Mar 08 '12 at 13:22

You're mixing PHP and SQL - for the IN SQL operator, you need a format like:

SELECT * from table WHERE comp_id IN (1,2)

So to get that in PHP you need to do something like:

$sql = "SELECT * from table Where comp_id IN (".implode(',',$arr).")"

Bear in mind that this only works if the array comprises of integers. You have to escape each element if they are strings.

score 2 · Answer 8 · answered Jul 05 '18 at 12:07

2

As per @barryhunter 's answer which works only on array that contains integer only:

$sql = "SELECT * from table Where comp_id IN (".implode(',',$arr).")";

I've made some tweaks to make it work for array of strings:

$sql = "SELECT * from table Where comp_id IN ('".implode("','",$arr)."')";

answered Jul 05 '18 at 12:07

Kelvin Barsana

824
12
28

clever solution for text string elements! works in Laravel, when using `DB::raw` – victorf Jul 18 '18 at 01:00

score 2 · Answer 9 · answered Mar 08 '12 at 13:21

2

You need something like:

$sql = "SELECT * from table where comp_id in (".implode(',',$arr.")";

answered Mar 08 '12 at 13:21

enygma

684
4
6

score 2 · Answer 10 · answered Mar 08 '12 at 13:22

You need to actually convert your $arr to a string. The simplest way with what you're doing would be to use implode()

$query = 'SELECT * from table Where comp_id IN (' . implode(',', $arr) . ')';

Right now if you echo your query you'll see that rather than the array being in the IN statement, it will just be the word "Array"

score 2 · Answer 11 · answered Mar 08 '12 at 13:22

2

You need to convert the array to a string for use in the query:

$list = implode(',', $arr);

Then it can be used in the IN clause:

SELECT * from table Where comp_id IN ($list)

answered Mar 08 '12 at 13:22

MrCode

63,975
10
90
112

not working for string values, since with implode you get one string and not separate string values as needed for the sql query – nh-labs Mar 28 '17 at 17:02
correct answer is here http://stackoverflow.com/questions/18072185/formatting-a-php-array-for-an-sql-in-clause – nh-labs Mar 28 '17 at 17:32

score 0 · Answer 12 · answered Aug 30 '18 at 01:49

There are some risks of SQL injection in a few of the previous answers. It might be fine if you are completely certain about $arr being sanitized (and will stay that way). But if you aren't completely sure, you might want to mitigate such risk using $stmt->bindValue. Here is one way of doing it:

# PHP
$in_list = array();
for ($i = 0; $i < count($arr); $i++) {
    $key = 'in_param_' . i;
    $in_list[':' . $key] = array('id' => $arr[$i], 'param' => $key);
}
$keys = implode(', ', array_keys($in_list));

// Your SQL ...
$sql = "SELECT * FROM table where id IN ($keys)";


foreach ($in_list as $item) {
    $stmt->bindValue($item['param'], $item['id'], PDO::PARAM_INT);
}
$stmt = $this->getConnection()->prepare($sql)->execute();

score 0 · Answer 13 · answered Jan 21 '19 at 18:05

If your array is of Integers :

$searchStringVar = implode(",",$nameIntAryVar);
$query="SELECT * from table NameTbl WHERE idCol='$idVar' AND comp_id IN ($searchStringVar)";

If your array is of Strings :

$searchStringVar = implode("','",$nameStringAryVar);
$query="SELECT * from table NameTbl WHERE idCol='$idVar' AND comp_id IN ('$searchStringVar')";

How to use php array with sql IN operator?

13 Answers13

Linked

Related