6

Here's my situation: I need to select all the messages where user_id = x OR y OR z.

I have an array in PHP:

users = ('1', '2', '3')

is there anyway to select all the messages where user_id = one of those values without having a massive query of:

user_id = '1' OR user_id = '2' OR user_id = '3?'

(I need to receive the messages of 100+ people so it would be inefficient)

Thanks

andy
  • 2,331
  • 1
  • 29
  • 48
  • Note that there is typically no difference in performance between IN and a bunch of OR comparisons. The ANSI standard defines IN to be the same as = ANY, which is supposed to use the normal = comparison operator in succession (thus following the usual NULL rules). – Cade Roux Jan 30 '12 at 15:59

6 Answers6

8

Yes! You can use the IN operator:

user_id IN ('1', '2', '3')

If your array will always be safe and contain elements, you can do:

"user_id IN ('" . implode("', '", $users) . "')"

in PHP, too.

Ry-
  • 209,133
  • 54
  • 439
  • 449
8

Use an IN clause.

SELECT *
    FROM YourTable
    WHERE user_id IN ('1','2','3')
Joe Stefanelli
  • 128,689
  • 18
  • 228
  • 231
  • 2
    I'll add because it may not be immediately clear: The contents of the IN clause can be another query. i.e `WHERE user_id IN (SELECT user_id FROM users WHERE user_type = 100)` – Ben English Jan 30 '12 at 15:54
6

This is easy to do :

$query = "SELECT * FROM table_name WHERE user_id IN('1','2','3')";

Since your value is in array you can use:

$users = array('1', '2', '3');
$user_id = "'" . implode("', '", $users ) . "'";
$query = "SELECT * FROM table_name WHERE user_id IN($user_id)";

Hope this helps.

Sabari
  • 6,025
  • 1
  • 24
  • 34
2

Probably you don't like IN keyword. Instead, you can use a regular expression like this: 

select * from your_table where user_id regexp '1|2|3'
mmdemirbas
  • 8,860
  • 5
  • 46
  • 52
1
user_id >= 1 AND <= 3

Is one alternative.

IsisCode
  • 2,452
  • 15
  • 20
  • The only problem with that is that I would want to select certain numbers so one time it may be: 1,2,4 instead of 1,2,3 – andy Jan 30 '12 at 17:05
0

before strings ids are:

$query = "SELECT * FROM table_name WHERE user_id IN('1','2','3')";

preformance int for ids:

$query = "SELECT * FROM table_name WHERE user_id IN(1,2,3)";
Kamil Dąbrowski
  • 1,067
  • 11
  • 13