MySQL gets a conversation between two users
I have a SQL table named private_messages with fields (id, from, to, message, stamp). the stamp field corresponds to the date of the message
So which query do I need:
1) get a conversation between two users (sorted by date)?
I tried request
(SELECT * FROM private_messages WHERE from=$my_id AND to=$other_id)
UNION
(SELECT * FROM private_messages WHERE from=$other_id AND to=$my_id)
ORDER BY stamp
;
but doesn't work ...
2) receive the latest messages between me and other users, each with a different user, sorted by date (for example, to create a mailbox, for example in a faceebook)?
1).
SELECT *
FROM private_messages a
WHERE (a.from = $my_id AND a.to = $other_id) OR
(a.from = $other_id AND a.to = $my_id)
ORDER BY stamp DESC
2.)
SELECT f.*
FROM
(
SELECT *
FROM private_messages a
WHERE (LEAST(a.from, a.to), GREATEST(a.from, a.to), a.stamp)
IN (
SELECT LEAST(b.from, b.to) AS x,
GREATEST(b.from, b.to) AS y,
MAX(b.stamp) AS msg_time
FROM private_messages b
GROUP BY x, y
)
) f
WHERE $my_id IN (f.from, f.to)
ORDER BY f.stamp DESC
Can you try this?
SELECT x.*
FROM (SELECT * FROM private_messages
WHERE `to`='$my_id' OR `from`='$my_id' GROUP BY `to`, `from`) AS x
ORDER BY x.stamp DESC ;
To
, From
can be reserved words . Noticed that it x
is a table alias.
I've done this in the past, but with a simple query. Maybe this will work for you.
SELECT * FROM private_messages WHERE (from=$my_id AND to=$other_id) OR (from=$other_id AND to=$my_id) ORDER BY stamp
1) Pretty sure you want quotes around your PHP variables, like:
(SELECT * FROM private_messages WHERE from='$my_id' AND to='$other_id') UNION (SELECT * FROM private_messages WHERE from='$other_id' AND to='$my_id') ORDER BY stamp DESC
2) Try something like:
SELECT * FROM (SELECT * FROM private_messages WHERE to='$my_id' OR from='$my_id' GROUP BY to, from) AS tmp_table ORDER BY stamp DESC