MySQL PHP - SELECT WHERE id = array()?

Question

Possible Duplicate:
MySQL query using an array
Passing an array to mysql

I have an array in PHP:

$array = array(1, 4, 5, 7);

As you can see, I have an array of different values, but I want to write a MYSQL statement that will check if the id is equal to any of the values in the array. For example, if a row has an id of 1, it will return that row, the same for 4, 5, and 7. The length of the array can vary due to the nature of the program, so that's where the problem is. Could I just do:

SELECT ...
  FROM ...
 WHERE id = '$array'

Or is there a better way?
If I was unclear, please ask me for more info.

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_in — jere, Feb 28 '12 at 03:58
See: http://meta.stackexchange.com/questions/2950/should-hi-thanks-taglines-and-salutations-be-removed-from-posts — OMG Ponies, Feb 28 '12 at 04:06

alex · Accepted Answer · 2012-02-28T04:00:30.183

136

Use IN.

$sql = 'SELECT * 
          FROM `table` 
         WHERE `id` IN (' . implode(',', array_map('intval', $array)) . ')';

edited Feb 28 '12 at 04:00

answered Feb 28 '12 at 03:58

alex

479,566
201
878
984

2

I guess `IN` is the preferred statement. Also, thanks for showing me how to put my array in there specifically. :) – Feb 28 '12 at 03:59
3

if i have same ids in array value then i get single time data i want multi time data then what should i do for that? – Dhara Patel Sep 15 '17 at 07:31
@DharaPatel Do you have items that share the same `id`? It may be a bad design decision to share ids (assuming they're primary). – alex Sep 18 '17 at 08:08
I would also like to point out what Dhara Patel said which might get missed. WHERE IN () will only run once for each data, so if you have more than once the same id in the array, it **will** get ignored! Additionally it is not a bad decision at all to have multiple ids, unless as stated are primary or unique of course – We're All Mad Here Dec 01 '17 at 20:28
does the IN act as a prepared statement. Assuming it doesn't, how would you use this in a prepared statement? – Steve Byrne Jan 07 '19 at 05:58
This is NOT vulnerable to SQL injection since it sanitizes inputs, but you MUST MUST MUST sanitize your inputs: the `array_map('intval', $array)` part of the answer. That expression is the most critical thing here. See also https://stackoverflow.com/a/9618304. Always sanitize your inputs even if it's coming from your own code--who knows what the next person who comes along will do. – flaviut Nov 17 '21 at 20:58

score 39 · Answer 2 · edited Aug 04 '15 at 14:35

39

What you are looking for is the IN() statement. This will test if a given field contains any of 1 or more values.

SELECT * FROM `Table` WHERE `id` IN (1, 2, 3)

You can use a simple loop to convert your $array variable into the proper format. Be sure to be mindful of SQL injection if your array values are coming from the front end.

edited Aug 04 '15 at 14:35

Ram Sharma

8,676
7
43
56

answered Feb 28 '12 at 03:57

Nathan Taylor

24,423
19
99
156

score 20 · Answer 3 · answered Feb 28 '12 at 03:58

20

Simply use the ID IN () syntax:

$sql = "SELECT FROM `table` WHERE `ID` IN (".implode(',',$array).")";

answered Feb 28 '12 at 03:58

Ben D

14,321
3
45
59

I know it's an old post but, Ben's answer worked for me since my array was coming from another while loop Thanks for your code Ben. – Samuel Ramzan Jan 03 '20 at 05:14
this is vulnerable to SQL injection and should not be used. – flaviut Nov 17 '21 at 20:56

score 4 · Answer 4 · answered Feb 28 '12 at 03:58

4

You can write your query like this:

select * from table where id in (1, 4, 6, 7)

You can add as many values as you need.

answered Feb 28 '12 at 03:58

Mosty Mostacho

42,742
16
96
123

score 1 · Answer 5 · edited Feb 29 '12 at 04:42

1

You'll want to use the IN operator:

$sql = 'SELECT FROM WHERE id IN (' . implode(",", $array) . ')';

There may be a limit in MySQL on how many values you can use in a list with the IN operator.

edited Feb 29 '12 at 04:42

yoozer8

7,361
7
58
93

answered Feb 28 '12 at 04:02

David Faber

12,277
2
29
40

this is vulnerable to SQL injection and should not be used. – flaviut Nov 17 '21 at 20:53
@user60561 it's only vulnerable to SQL injection if the array contents are user-supplied or otherwise potentially hold injected SQL. The question that was asked was how to query IDs based on an array of declared integers. A comment that mentioned that "people should make sure that the array doesn't include inject-able values" is appropriate, but for cases like the OP asked you absolutely should use this answer. – Ben D Nov 18 '21 at 23:05
1

Doesn't matter--you should always fully escape data/use prepared statements everywhere. No matter how safe you think it is now. Same reason reason electricians don't use thin wire on lighting circuits: sure you'd probably be fine with it now, but someone changes the code elsewhere & now you have a house fire. – flaviut Nov 20 '21 at 05:32

score 0 · Answer 6 · answered Feb 28 '12 at 03:58

0

You can SELECT WHERE id IN (item1, item2...). Just loop through your PHP array to build your array for the query.

answered Feb 28 '12 at 03:58

yoozer8

7,361
7
58
93

MySQL PHP - SELECT WHERE id = array()?

6 Answers6

Linked

Related