Using IN to search an database with an array

Question

I have an SQL statement:

$sql = "SELECT * FROM user_events WHERE userID =" . $uid . " OR groupID = (IN ($gId) WHERE userID IS NULL) ORDER BY timestamp DESC";

This looks like this when printed:

SELECT * FROM user_events WHERE userID = 34 OR groupID =(IN (44,45) WHERE uID IS NULL) ORDER BY timestamp DESC

What I'm trying to do is select from the database any row where either the userId matches a specific variable or where the groupID matches a CSV variable where the userID is NULL.

Can anyone explain what I'm doing wrong here? I'm quite stuck!

Use IN as an operator when dealing with comma-separated lists. Use = as an operator when comparing exact values. Use LIKE as an operator when comparing case-insensitive values and wildcards. They are all comparison operators and should be used as such (just like in mathematics). — Matt
– Matt, Commented Aug 29, 2012 at 14:44
Also, use prepared statements, don't concatenate strings to build a query. — InternetSeriousBusiness
– InternetSeriousBusiness, Commented Aug 29, 2012 at 14:45
If you use placeholders, a lot of the SQL query encoding issues will be handled for you. Seeing things like $uid inside a query should set your hair on fire. — tadman
– tadman, Commented Aug 29, 2012 at 15:06

Ry- · Accepted Answer · 2012-08-29 14:43:09Z

9

IN is an operator, not a... that. You also want AND, not WHERE.

$sql = "SELECT * FROM user_events
        WHERE userID = $uid
           OR groupID IN ($gId) AND userID IS NULL
        ORDER BY timestamp DESC";

answered Aug 29, 2012 at 14:43

Ry-♦

226k56 gold badges496 silver badges504 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mingo · Accepted Answer · 2012-08-29 14:44:11Z

1

$sql = "SELECT * FROM user_events 
       WHERE userID =" . $uid . " 
       OR (groupID IN ($gId) AND userID IS NULL) ORDER BY timestamp DESC";

answered Aug 29, 2012 at 14:44

Mingo

9664 gold badges12 silver badges23 bronze badges

Comments

Brian Hoover · Accepted Answer · 2012-08-29 14:44:59Z

0

I think that you want something like:

SELECT * FROM user_events 
WHERE (userID = 34 OR groupID IN (44,45)) and uID IS NULL
ORDER BY timestamp DESC

In PHP, this might look like:

$sql = "SELECT * FROM user_events WHERE (userID =". $uid . " OR groupID IN ($gId)) AND userID IS NULL ORDER BY timestamp DESC";

answered Aug 29, 2012 at 14:44

Brian Hoover

7,9652 gold badges31 silver badges41 bronze badges

Collectives™ on Stack Overflow

Using IN to search an database with an array

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related