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.
$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));
$stmt = $conn->query("SELECT messages.*, blockRequests.* FROM messages
LEFT JOIN blockRequests ON messageBox.user = blockRequests.reportedBy")
SELECT messages.*, blockRequests.* FROM messages
LEFT JOIN blockRequests ON messages.user = 'suser'
WHERE blockRequests.reportedBy IS NULL
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.