49

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.

Improve this question
2

6 Answers 6

Reset to default
138

Use IN.

$sql = 'SELECT * 
          FROM `table` 
         WHERE `id` IN (' . implode(',', array_map('intval', $array)) . ')';
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

I guess IN is the preferred statement. Also, thanks for showing me how to put my array in there specifically. :)
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?
@DharaPatel Do you have items that share the same id? It may be a bad design decision to share ids (assuming they're primary).
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
does the IN act as a prepared statement. Assuming it doesn't, how would you use this in a prepared statement?
|
42

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.

Improve this answer

Comments

20

Simply use the ID IN () syntax:

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

2 Comments

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.
this is vulnerable to SQL injection and should not be used.
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.

Improve this answer

Comments

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.

Improve this answer

3 Comments

this is vulnerable to SQL injection and should not be used.
@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.
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.
0

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

Improve this answer

Comments