Get all data from one table that's not in another table MySQL

Question

I have a messages table:

  mysql> describe messages;
+-----------+--------------+------+-----+-------------------+-----------------------------+
| Field     | Type         | Null | Key | Default           | Extra                       |
+-----------+--------------+------+-----+-------------------+-----------------------------+
| id        | int(11)      | NO   | PRI | NULL              | auto_increment              |
| user1_id  | int(11)      | NO   |     | NULL              |                             |
| user1     | varchar(255) | NO   |     | NULL              |                             |
| user2_id  | int(11)      | NO   |     | NULL              |                             |
| user2     | varchar(255) | NO   |     | NULL              |                             |
| message   | text         | YES  |     | NULL              |                             |
| timestamp | timestamp    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| user1read | varchar(3)   | NO   |     | NULL              |                             |
| user2read | varchar(3)   | NO   |     | NULL              |                             |
+-----------+--------------+------+-----+-------------------+-----------------------------+
9 rows in set (0.00 sec)

And then I have a blocked table:

 mysql> describe blocked;
+-----------------+---------+------+-----+---------+-------+
| Field           | Type    | Null | Key | Default | Extra |
+-----------------+---------+------+-----+---------+-------+
| user_id         | int(11) | NO   | PRI | NULL    |       |
| blocked_user_id | int(11) | NO   | PRI | NULL    |       |
+-----------------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

What I am trying to do is get all of the data to display in separate divs on a page. This script is called every second. The query I ended up with yielded all messages, no matter the blocked status.

So, let's say my ID is 1, and a blocked user's ID is 5, user 5 should not show up in the list. The same would go for the other user if they logged in.

After messing with that query a while, I attempted a 2nd query. That was even worse because nothing appeared on the page, despite data being returned with manual SQL entries using the same query.

The file is rather small, so I will just post the entire thing:

Edit: The first query is actually displaying all messages, including myself as a message, which is obviously wrong. It appears my queries are more broken than I thought.

 <?php
session_start();

include '../../../config/DB.php';

$username = $_SESSION['logged_in']; //to use in queries

try {
    $db = new DB(); //new DB object
} catch (Exception $e) {
    $e->getMessage();
}

try {
    $names = array(); //to store during foreach iterations

    //get the id for the other query attempt
    $id_result = $db->getRow('SELECT id FROM users WHERE username=?', [$username]);
    $id = $id_result['id'];

    foreach ($messages_result = $db->getRows('SELECT messages.user1, messages.user2, messages.timestamp, messages.message, messages.user2read, users.avatar
                                                    FROM messages
                                                    LEFT JOIN users ON messages.user1 = users.username
                                                    WHERE messages.user2 = ? AND 
                                                    (users.id NOT IN (SELECT user_id FROM blocked))
                                                    OR (users.id NOT IN (SELECT blocked_user_id FROM blocked))
                                                    ORDER BY timestamp DESC', [$username]) as $result) {
        $sender = $result['user1'];
        $time = $result['timestamp'];
        $message = $result['message'];
        $avatar = $result['avatar'];
        $user2read = $result['user2read'];

        //Do this so users will only show once.
        //One div per user, and when clicked
        //all messages are shown elsewhere on a page.
        if (!in_array($sender, $names)) {
            $names[] = $sender;

            //If the message is unread, show name in bold
            //Else show regular text
            //$avatar has been removed from the html for now
            //It shows up in an <img> tag
            if ($user2read === 'no') {
                echo '<div id="single_message" data-sender="' . $sender . '"><p style="padding-left:8px;"><p><a class="link" style="font-weight:bold;font-size:16px;" href=' . $sender . '>' . $sender . '</a></strong></p><p style="white-space:pre-wrap;margin-left:8px;margin-right:8px;">' . $message . '</p><p style="padding-left:8px;border-bottom: 1px solid #ccc;">' . $time . '</p></div>';
            } else {
                echo '<div id="single_message" data-sender="' . $sender . '"><p style="padding-left:8px;"><p><a class="link" href=' . $sender . '>' . $sender . '</a></p><p style="white-space:pre-wrap;margin-left:8px;margin-right:8px;">' . $message . '</p><p style="padding-left:8px;border-bottom: 1px solid #ccc;">' . $time . '</p></div>';
            }

        } else {
            continue;
        }
    }

} catch (Exception $e) {
}

Here is the 2nd query I tried (I'll just show the foreach). This is the one that shows nothing on the page.

 foreach($messages_result = $db->getRows('SELECT * FROM messages m LEFT JOIN blocked b ON ((m.user1_id = b.user_id OR m.user2_id = b.user_id)
                                                    AND (m.user1_id = b.blocked_user_id OR m.user2_id = b.blocked_user_id))
                                                    WHERE (m.user1_id = 1 OR m.user2_id = 1) HAVING m.user_id IS NULL
                                                    ORDER BY timestamp DESC', [$id]) as $result) {

Am I missing something here?

Answer 1

WHERE (m.user1_id = 1 OR m.user2_id = 1) HAVING m.user_id IS NULL i think its contradicting each other bcos you have this on join ON ((m.user1_id = b.user_id OR m.user2_id = b.user_id) AND (m.user1_id = b.blocked_user_id OR m.user2_id = b.blocked_user_id))....its like canceling each other........

Rectify this....maybe can....good luck

Answer 2

A left join on blocked users with matching of user2 ("me") to blocked.user_id, the person who blocks, and user1 against blocked.blocked_user_id the user who is blocked, if the result is NULL (aka b.user_id IS NULL), then there is no block.

foreach($messages_result = $db->getRows('
SELECT messages.user1, messages.user2, messages.timestamp, messages.message, messages.user2read, users.avatar
FROM messages
LEFT JOIN users ON messages.user1 = users.username
LEFT JOIN blocked b ON b.user_id = messages.user2_id AND b.blocked_user_id = messages.user1_id
WHERE messages.user2 = ? AND b.user_id IS NULL
ORDER BY timestamp DESC',[$username]) as $result) {
...

Answer 3

I have a similar script that gets all users from the users table that are not blocked, versus getting data from the messages table. Since this compared only the user id with the blocked ids, I was able to do it with one query block. However, I needed to check all possibilities between user1_id and user2_id from messages, and user_id and blocked_user_id from blocked. This required an additional block which was a reversed version of the first block.

I will show how this was successful by showing the id and username from users, then everyone from the blocked table, then the results using ids that are in the blocked table.

mysql> select id, username from users;
+----+-----------------+
| id | username        |
+----+-----------------+
|  1 | csheridan       |
|  2 | testuser        |
|  3 | testuser2       |
|  4 | washington_user |
+----+-----------------+
4 rows in set (0.00 sec)

mysql> select * from blocked;
+---------+-----------------+
| user_id | blocked_user_id |
+---------+-----------------+
|       1 |               2 |
|       1 |               4 |
+---------+-----------------+
2 rows in set (0.00 sec)

mysql> SELECT m.* FROM messages m   
       WHERE NOT EXISTS (SELECT 1 FROM blocked b 
       WHERE b.user_id = m.user1_id AND b.blocked_user_id = 1) 
       AND NOT EXISTS 
       (SELECT 1 FROM blocked b WHERE b.blocked_user_id = m.user1_id AND b.user_id = 1)   
       AND NOT EXISTS (SELECT 1 FROM blocked b WHERE b.user_id = m.user2_id AND b.blocked_user_id = 1) 
       AND NOT EXISTS (SELECT 1 FROM blocked b WHERE b.blocked_user_id = m.user2_id AND b.user_id = 1) 
       ORDER BY timestamp DESC;
+----+----------+-----------+----------+-----------+----------------+---------------------+-----------+-----------+
| id | user1_id | user1     | user2_id | user2     | message        | timestamp           | user1read | user2read |
+----+----------+-----------+----------+-----------+----------------+---------------------+-----------+-----------+
|  2 |        3 | testuser2 |        1 | csheridan | Hey!           | 2018-11-14 12:12:35 | yes       | no        |
|  4 |        3 | testuser2 |        1 | csheridan | Are you there? | 2018-11-14 12:12:35 | yes       | no        |
+----+----------+-----------+----------+-----------+----------------+---------------------+-----------+-----------+
2 rows in set (0.00 sec)

mysql> SELECT m.* FROM messages m   
       WHERE NOT EXISTS (SELECT 1 FROM blocked b  
       WHERE b.user_id = m.user1_id AND b.blocked_user_id = 2) 
       AND NOT EXISTS (SELECT 1 FROM blocked b WHERE b.blocked_user_id = m.user1_id AND b.user_id = 2)   
       AND NOT EXISTS (SELECT 1 FROM blocked b WHERE b.user_id = m.user2_id AND b.blocked_user_id = 2) 
       AND NOT EXISTS (SELECT 1 FROM blocked b WHERE b.blocked_user_id = m.user2_id AND b.user_id = 2) 
       ORDER BY timestamp DESC;
+----+----------+-----------------+----------+----------+---------------------+---------------------+-----------+-----------+
| id | user1_id | user1           | user2_id | user2    | message             | timestamp           | user1read | user2read |
+----+----------+-----------------+----------+----------+---------------------+---------------------+-----------+-----------+
|  7 |        4 | washington_user |        2 | testuser | Hey man! What's up? | 2018-11-14 14:32:27 | yes       | no        |
|  8 |        4 | washington_user |        2 | testuser | Hello there.        | 2018-11-14 14:32:27 | yes       | no        |
+----+----------+-----------------+----------+----------+---------------------+---------------------+-----------+-----------+
2 rows in set (0.00 sec)

So, the problem was that I was on the right track, I just needed another block. First, I checked on blocked.user_id = messages.user1_id AND blocked.user_id = ?, then I checked the other condition for user2_id. Unless someone comes up with a more compact query, this is what I needed. If I only did the first block, it did not check all conditions and still returned blocked users since it didn't check both user1_id and user2_id from messages.