CODEWITHSUNDEEP
mysql
user3051755
user3051755

Reputation: 1557

Single SQL query for selecting chats

I need to select all chats and right now I'm selecting them using multiple queries like this:

        $query_1 = mysqli_query($database, "SELECT chat_id, user_id_1, user_id_2
                                                     FROM chat
                                                     WHERE user_id_1 = '$user_id' OR user_id_2 = '$user_id')");

        if (mysqli_num_rows($query_1) > 0) {
            while ($row = mysqli_fetch_assoc($query_1)) {
                $chat_id = $row['chat_id'];
                $user_id_1 = $row['user_id_1'];
                $user_id_2 = $row['user_id_2'];

                if ($user_id_1 == $user_id)
                    $user_id_chat = $user_id_2;
                else
                    $user_id_chat = $user_id_1;

                $query_2 = mysqli_query($database, "SELECT username
                                                                FROM user
                                                                WHERE user_id = '$user_id_chat'");

                $row2 = mysqli_fetch_assoc($query_2);
                $username = $row2['username'];

                $query_3 = mysqli_query($database, "SELECT text,
                                                                (SELECT COUNT(message_id) FROM message WHERE chat_id = '$chat_id' AND user_id != '$user_id' AND seen = '0') AS unread_messages
                                                                FROM message
                                                                WHERE chat_id = '$chat_id'
                                                                ORDER BY message_id DESC
                                                                LIMIT 1");

                $row3 = mysqli_fetch_assoc($query_3);
                $text = $row3['text'];
                $unread_messages = $row3['unread_messages'];
            }
        }

Is there a way to get all this ($chat_id, $user_id_chat, $username, $text and $unread_messages) using only one query?

EDIT: Added example data and expected output:

Table user

user_id     username     ...
   1          User1      ...
   2          User2      ...
   3          User3      ...
   4          User4      ...


Table chat

chat_id     user_id_1     user_id_2     ...
   1            3            4          ...
   2            1            3          ...


Table message

message_id     chat_id     user_id     text     seen     ...
    1             1           4        Hi!       1       ...
    2             2           1        Hello     1       ...
    3             1           3        hi        0       ...
    4             2           3        Hi        0       ...
    5             2           3     How are you? 0       ...

If $user_id is 1 there should be only one chat and value of $chat_id should be 2, of $user_id_chat should be 3, of $username should be 'User3', of $text should be 'How are you?' and of $unread_messages should be 2.

Upvotes: 0

Views: 124

Answers (2)

Alex
Alex

Reputation: 17289

http://sqlfiddle.com/#!9/8c6cb/8

$query_1 = mysqli_query($database, 
      "SELECT
         c.chat_id,
         u.username,
         COALESCE(m.text,'MY PLACEHOLDER'),
         COALESCE(um.unread_messages,0)
      FROM (
         SELECT chat_id, 
                IF(user_id_1 = '$user_id', user_id_2, user_id_1) user_id_chat
         FROM chat
         WHERE user_id_1 = '$user_id' OR user_id_2 = '$user_id'
      ) c
      LEFT JOIN user u
      ON u.user_id = c.user_id_chat
      LEFT JOIN (SELECT chat_id, SUM(seen = '0',1,0) unread_messages, MAX(message_id) max_id
           FROM message 
           WHERE  user_id != '$user_id' 
           GROUP BY chat_id) um
      ON um.chat_id = c.chat_id
      LEFT JOIN message m
      ON m.chat_id = c.chat_id
        AND m.message_id = um.max_id ");

if (mysqli_num_rows($query_1) > 0) {
  while ($row = mysqli_fetch_assoc($query_1)) {
    $chat_id = $row['chat_id'];
    $username = $row['username'];
    $text = $row ['text'];
    $unread_messages = $row['unread_messages'];
  }
}

Upvotes: 1

mzcoxfde
mzcoxfde

Reputation: 235

So, you are looking for a way to select from two tables in one query. Then you might like INNER JOIN. Visit this link:

http://www.w3schools.com/sql/sql_join_inner.asp

OR

Make a function to select like this:

` function select ($query ="SELECT * FROM $table", $limit = 1) {

    $row = mysql_query($query);
    // I'm so lazy to type the other codes, just put ur while loop and other stuff(if any) here

}    `

This then must be called as:

` select("SELECT chat_id, user_id_1, user_id_2 FROM chat WHERE user_id_1 = '$user_id' OR user_id_2 = '$user_id')");

select(" (SELECT COUNT(message_id) FROM message WHERE chat_id = '$chat_id' AND user_id != '$user_id' AND seen = '0') AS unread_messages
                                                            FROM message
                                                            WHERE chat_id = '$chat_id' AND user_id != '$user_id'
                                                            ORDER BY message_id DESC
                                                            LIMIT 1");    `

This won't need multiple where loops.

Sorry for bad code layout, I'm a newbie here. Good Luck!

Upvotes: 0

Related Questions