I have a table called last_msg, in there i store the last mensage from a private chat between two users, and i update the column from and to when I send a new mensage. I use this table to show a list of mensages like facebook. I also use this table to another things, so i would rather fix the problem described as bellow.
Because of the ON users.user_id = last_msg.from i get data only from who is sending the mensage, this was the best i got... This is my current sql:
SELECT `last_msg`.`msg`, `last_msg`.`from`, `users`.`username`, `users`.`avatar`
FROM `last_msg`
INNER JOIN `users`
ON `users`.`user_id` = `last_msg`.`from`
WHERE `last_msg`.`to` = :user_id_logged OR `last_msg`.`from` = :user_id_logged_2
On the INNER JOIN users I want to get data only from the other user that i'm talking to in the chat, and the data from last_msg can be from both sender and receiver, as the facebook does.
So i tried:
SELECT `last_msg`.`msg`, `last_msg`.`from`, `users`.`username`, `users`.`avatar`
FROM `last_msg`
INNER JOIN `users`
ON `users`.`user_id` != :user_logged
WHERE (`last_msg`.`to` = :user_logged_2 OR `last_msg`.`from` = :user_logged_3)
But it did not work, it's returning a list of all users in the table users. Any suggestions about how can i fix it?
You can try joining the users table two times, one for the from user and the other for the to user like shown below, also note you need to use LEFT Join in order to get all from and to users.
SELECT `last_msg`.`msg`, `last_msg`.`from`, `FromUser`.`username`, `FromUser`.`avatar`,`ToUser`.`username`,`ToUser`.`avatar`
FROM `last_msg`
LEFT JOIN `users` as `FromUser`
ON `FromUser`.`user_id` = `last_msg`.`from`
LEFT JOIN `users` as `ToUser`
ON `ToUser`.`user_id` = `last_msg`.`to`
WHERE `last_msg`.`to` = :user_id_logged OR `last_msg`.`from` = :user_id_logged_2";
Updated Code
SELECT `last_msg`.`msg`, `last_msg`.`from`, `users`.`username`, `users`.`avatar`
FROM `last_msg`
INNER JOIN `users`
ON `users`.`user_id` =`last_msg`.`to`
WHERE `last_msg`.`from`= :user_logged
UNION
SELECT `last_msg`.`msg`, `last_msg`.`to`, `users`.`username`, `users`.`avatar`
FROM `last_msg`
INNER JOIN `users`
ON `users`.`user_id` =`last_msg`.`from`
WHERE `last_msg`.`to`= :user_logged
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments