PHP/MySQL query to exclude results

Question

I have a members search function on my website. What it basically does is getting some info from the leden table and show it for each member found, except for yourself.

The $id is the person that's logged in.

Right now, this is my current query:

$sql = "
SELECT ldn.schermnaam, ldn.geboortedatum, ldn.userid, ldn.foto, ldn.oproep
FROM leden AS ldn
WHERE ldn.userid != $id
";

This works fine.

Now what I want is to also exclude members that are blocked by you. The blockedmembers table looks like this:

- ID
- Useridsender (the one that blocked the other one)
- Useridreceiver (the one that has been blocked)
- Creation date

Now I want to exclude the blocked members and don't show them in the result list.

I tried using INNER JOIN and LEFT JOIN, but haven't figured out how to get a good query. Anyone knows how my query should look like?

p.s. it's a dynamic query, so based on the user input AND statements are added at the end.

Dave · Accepted Answer · 2013-06-26 11:36:25Z

6

$sql = "
SELECT ldn.schermnaam, ldn.geboortedatum, ldn.userid, ldn.foto, ldn.oproep
FROM leden AS ldn
WHERE ldn.userid != $id AND ldn.userid NOT IN(SELECT Useridreceiver FROM blocktable WHERE Useridsender=$id)
";

this should be pretty close I'm using a sub select but it can also be done using an outer join I think

answered Jun 26, 2013 at 11:36

Dave

3,2882 gold badges25 silver badges40 bronze badges

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

2 Comments

Ivan Yonkov Over a year ago

I think any join can be used, or at least any different from INNER JOIN, if the table is empty by default

Nick Over a year ago

Since you are the first one...I'll thank you! Works great, thanks :D Can't upvote you yet. Also, thanks to the rest!

Waygood · Accepted Answer · 2013-06-26 11:45:57Z

3

A lot of people are answering, using sub selects. Here's mine using LEFT JOIN (without subselect)

You can use LEFT JOIN to select matches, then extract out entries with no match (joined table id IS NULL)

SELECT ldn.schermnaam, ldn.geboortedatum, ldn.userid, ldn.foto, ldn.oproep
FROM leden AS ldn LEFT JOIN blockedmembers AS blm
  ON (blm.Useridsender=$id AND ldn.userid=blm.Useridreceiver)
WHERE ldn.userid != $id 
AND blm.ID IS NULL

answered Jun 26, 2013 at 11:45

Waygood

2,6832 gold badges17 silver badges16 bronze badges

1 Comment

Dave Over a year ago

+1 for providing the join based version since I'm too lazy to do it myself :)

M Khalid Junaid · Accepted Answer · 2013-06-26 11:46:19Z

1

Filter results from blockedmembers tables also

$sql="SELECT ldn.schermnaam, ldn.geboortedatum, ldn.userid, ldn.foto, ldn.oproep
FROM leden AS ldn
WHERE ldn.userid != $id AND ldn.userid NOT IN 
(SELECT Useridreceiver FROM blockedmembers WHERE Useridsender =$id)";

edited Jun 26, 2013 at 11:46

answered Jun 26, 2013 at 11:38

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

1 Comment

M Khalid Junaid Over a year ago

@RoyalBg now added the purpose

Community · Accepted Answer · 2017-05-23 12:06:49Z

1

You have working answers using NOT IN however nobody has suggested NOT EXISTS, which could be a better performing option for you.

Take a look here: NOT IN vs NOT EXISTS

SELECT 
  ldn.schermnaam, ldn.geboortedatum, ldn.userid, ldn.foto, ldn.oproep
FROM 
  leden AS ldn
WHERE 
  ldn.userid != $id
AND
  NOT EXISTS (
    SELECT 1 
    FROM blockedmembers AS bm 
    WHERE 
      bm.Useridreceiver = ldn.userid
    AND
      bm.Useridsender = $id
  )

edited May 23, 2017 at 12:06

CommunityBot

11 silver badge

answered Jun 26, 2013 at 11:46

AlexP

9,8571 gold badge28 silver badges44 bronze badges

Collectives™ on Stack Overflow

PHP/MySQL query to exclude results

4 Answers 4

2 Comments

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related