CODEWITHSUNDEEP
phpmysqlmysqli
Brent Morse
Brent Morse

Reputation: 19

Php MySql Statement using multiple AND Operators

I am trying to write a php mysql statement using multiple AND Operators and can't seem to get it to work. Is there a simple fix for this?

$sql = 'SELECT user_id FROM groups WHERE group_id = 11 AND group_id = 17 AND group_id = 19 AND group_id = 20';
$res = mysqli_query($gmg_conn, $sql);

This query has to return each user that is in all of the groups mentioned. For example the DB table looks as follows.

group_id | user_id  
11       | 800  
17       |  800  
19       |  800  
20       |  800

So as you can see if I run this query: 

$sql = 'SELECT user_id FROM groups WHERE group_id IN (11,17,19,20)';

It will return all users that are in 1 of those groups. I need something that will search for users who are apart of all 4 groups. The other method I have is to bring in each user 1 by 1 and pull their groups and then do the if statement in php but I would like to seek a MySql query for this if possible.

@Alex who is located in the comments section was able to help me with exactly what I needed.

SELECT (COUNT(group_id) = 3) AS matches, user_id AS user 
FROM groups 
WHERE group_id IN (12, 18, 14) 
GROUP BY user_id HAVING matches <> 0;

Upvotes: 0

Views: 771

Answers (4)

Halfpint
Halfpint

Reputation: 4079

When MySQL runs your query it would evaluate like this:

Table (assume only 3 records exist):
====================================
group_id 
11         <-- Contains 11 AND 17 AND 19 AND 20? FALSE
17         <-- Contains 11 AND 17 AND 19 AND 20? FALSE
19         <-- Contains 11 AND 17 AND 19 AND 20? FALSE

What you want to use is the OR operator or IN method, this causes the evaluation to happen like this:

Table (assume only 3 records exist):
====================================
group_id 
11         <-- Contains 11 OR 17 OR 19 OR 20       ? TRUE (Or query)
17         <-- Value In collection (11, 17, 19, 20)? TRUE (In query)
19         <-- Contains 11 OR 17 OR 19 OR 20       ? TRUE (Or query)

Therefore your amended query would look like:

SELECT user_id FROM groups WHERE group_id IN (11,17,19,20)

OR

SELECT user_id FROM groups WHERE group_id = 11 OR group_id = 17 OR group_id = 19 OR group_id = 20

Under the hood, the IN() method handles the OR chaining for you, therefore I'd recommend the first implementation.

EDIT In order to get all users which belong to each of the specified groups, you can utilise a COUNT method and then perform a check in the GROUP BY segment:

SELECT (COUNT(group_id) = 3) AS matches, user_id AS user 
FROM groups 
WHERE group_id IN (12, 18, 14) 
GROUP BY user_id HAVING matches <> 0;

Alternatively this query would work too:

SELECT user_id AS user 
FROM groups 
WHERE group_id IN (12, 18, 14) 
GROUP BY user_id HAVING (COUNT(group_id) = 3);

Lets break this down so you can understand it

Line 1: (COUNT(group_id) = 3) - this will return 1 (true) if the query returns a value which is 3 (or whatever the size of your collection is)

Line 3: Performs the OR check we saw earlier, but will only return 1 if the COUNT is 3

Line 4: List the users who match the criteria, ONLY when 1 is returned (<> is the != operator in SQL)

I hope this helps you - Updated fiddle here :)

Upvotes: 2

Sumit Gupta
Sumit Gupta

Reputation: 2192

Did someone tell you that 'and' means that both condition should be matched?

in your case you should either use 'or' because if group_id is 11 it cannot be 17 and hence in any case it fails. But to rewrite your query it is better to use in

SELECT user_id FROM groups WHERE group_id in (11, 17, 19,20)

Hope that help

Upvotes: 0

Clown Man
Clown Man

Reputation: 393

use the IN operator.

$sql = 'SELECT user_id FROM groups WHERE group_id IN (11,17,19,20)'; $res = mysqli_query($gmg_conn, $sql);

It's because it's looking for group_id that has all 4 values set at once, so it wont show any results.

Upvotes: 1

Martin
Martin

Reputation: 6687

You probably want WHERE group_id IN (11, 17, 19, 20);, your current query isn't logically possible because a field can not have multiple values at the same time.

Upvotes: 0

Related Questions