CODEWITHSUNDEEP
phpmysqlsqlselect
jpceia
jpceia

Reputation: 113

MySQL get 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 what query do i need for:

1) get a conversation between two users (ordered by date)?

I have tried the query 

(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) get the last messages beetween me and other users, each one with a different user, ordered by date (to construct a inbox like in faceebook for example)?

Upvotes: 3

Views: 3154

Answers (4)

bonCodigo
bonCodigo

Reputation: 14361

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 could be reserved words. Noticed that x is a table alias.

Upvotes: 1

Madison Brown
Madison Brown

Reputation: 331

1) Pretty sure you need quotes around your PHP variables, like this: 

(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

Upvotes: 0

hasnainwasaya
hasnainwasaya

Reputation: 11

I had done this in past, but with simple query. May be 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

Upvotes: 0

John Woo
John Woo

Reputation: 263943

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

Upvotes: 8

Related Questions