Clever Geek Handbook

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)?

+3


source to share


4 answers


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
+9


source


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.

+1


source


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
0


source


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
0


source






More articles:


All Articles