Filter MySQL Table Results based on List in Another Table

Question

I have a table that is giving me all of my table results data in the form of a json object array. This result returns the entire "messages" table data.

The goal is for the logged in user $myusername to see all results except if a person appears in the 'blockRequests' table where $myusername is the reportedBy.

My php:

$myusername = $_GET['username']; //the logged in user who is viewing the messages

$rows = array();
$stmt = $conn->query("SELECT * FROM messages ORDER BY datetime DESC");
while($r = $stmt->fetch(PDO::FETCH_ASSOC))
{
    $rows[] = $r;
}
print json_encode(array('messageData' => $rows));

I have a seperate table 'blockedRequests' that lists requests to block users, where a user reports the persons username as 'blockedUsr'.

I am simply trying to return ALL messages but exclude any 'blockedUsr' that may appear in the blockedRequests table.

My SQL knowledge is so basic I can't figure out how to join the tables and get the desired results.

What I tried

$stmt = $conn->query("SELECT messages.*, blockRequests.* FROM messages 
    LEFT JOIN blockRequests ON messageBox.user = blockRequests.reportedBy")

This returns the messages lists joined but not filtered. Any advice on how to join these tables to get the filtered messages would be great.

Edit (what I just tried)

SELECT messages.*, blockRequests.* FROM messages
 LEFT JOIN blockRequests ON messages.user = 'suser'
WHERE blockRequests.reportedBy IS NULL

This gives me the joined table but does not filter blockedUsrs, I still see them in the result set.

Try WHERE blockRequests.blockedUser != messageBox.user or join on those fields — Mees Kluivers
– Mees Kluivers, Commented Apr 18, 2016 at 16:03
but shouldn't $myusername be in the where clause somewhere? This is where im hung up at. The messages table needs to filter based on $myusername somehow — MsNichols
– MsNichols, Commented Apr 18, 2016 at 16:06
Yes, I didn't see that but you can just add that as a prepared statement php.net/manual/en/pdo.prepared-statements.php — Mees Kluivers
– Mees Kluivers, Commented Apr 18, 2016 at 16:08
I fear your question isn't quite clear. Do you want a resultset that shows all messages rows matching a particular user except those messages for which message.id appears in any blockRequests.msgid value? In your application, are messages blocked, or users? Presumably you're looking for all messages to a particular user except those that come from blocked users? If that's the case, I don't see how to tell the to and from users. Please consider editing your question to clarify. — O. Jones
– O. Jones, Commented Apr 18, 2016 at 16:27

karina · Accepted Answer · 2016-04-18 16:44:44Z

2

Perhaps you could try

SELECT messages.*
FROM messages
WHERE messages.userid NOT IN (SELECT blockedusr 
                              FROM blockrequests 
                              WHERE reportedBy = $myusername);

Where obviously you need to figure out how to pass $myusername to the query via your orm.

Basically it reads: Select all the messages where the message user id is not in the list of blocked users reported by this user.

In this case we aren't using a join... i didn't feel it was necessary from what you are looking for. I just used a sub select and the "NOT IN" clause.

answered Apr 18, 2016 at 16:44

karina

8055 silver badges10 bronze badges

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

1 Comment

MsNichols Over a year ago

THIS WORKED!!!!!!!! my messages table is now filtering correctly excluding the blockeUsr(s)... THANK YOU SO MUCH FOR THIS!

O. Jones · Accepted Answer · 2016-04-18 16:41:45Z

0

The pattern you're looking for is ...

 FROM left_side_table
 LEFT JOIN right_side_table ON left_side_table.column = right_side_table.column
WHERE right_side_table.column IS NULL
  AND left_side_table.some_other_column = 'value'

This excludes the rows from left_side_table that matched one or more rows in right_side_table in the ON condition of your LEFT JOIN. Edit: You can add other conditions to the WHERE clause with AND as needed.

Notice this: your ON clause ordinarily mentions columns from both tables being joined. If it doesn't, the JOIN operation simply generates all possible pairs of rows from the two tables. If both tables are large, that makes for a very large result set.

It's a little counterintuitive until you get used to it.

In your application you may want this:

SELECT messages.*
  FROM messages
  LEFT JOIN blockRequests ON messages.user = blockRequests.user
 WHERE blockRequests.reportedBy IS NULL
   AND messages.usr = 'suser'

Two things here: First, it makes no sense to SELECT ... blockRequests.* in this query. Those columns are guaranteed, by the IS NULL clause, to be null. Boring.

Second, I don't understand the relationship between your messages and blockRequests tables. For the LEFT JOIN to work correctly, that relationship has to be mentioned in the ON clause. Therefore, my suggested ON clause is a guess.

edited Apr 18, 2016 at 16:41

answered Apr 18, 2016 at 16:10

O. Jones

110k17 gold badges135 silver badges188 bronze badges

1 Comment

MsNichols Over a year ago

but how do i filter this based on logged in user $myusername how do i include it in the where clause?

Collectives™ on Stack Overflow

Filter MySQL Table Results based on List in Another Table

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related