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,369
  • 2
  • 31
  • 50
  • 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-
  • 218,210
  • 55
  • 464
  • 476
8

Use an IN clause.

SELECT *
    FROM YourTable
    WHERE user_id IN ('1','2','3')
Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
  • 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,205
  • 1
  • 27
  • 36
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
  • 9,060
  • 5
  • 45
  • 53
1
user_id >= 1 AND <= 3

Is one alternative.

IsisCode
  • 2,490
  • 18
  • 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
  • 984
  • 11
  • 17