MizAkita MizAkita - 2 years ago 91
SQL Question

Filter MySQL Table Results based on List in Another Table

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.

enter image description here

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.

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download