PHP/MYSQL - showing newest distinct value from multiple columns

Question

This is my mysql table structure:

msgid | senderid | sender | recipientid | recipient | title | message | date

I'm looking to present in inbox for my private messaging system as Facebook have done. That is, each conversation is output ONE TIME from the table irrespective of whether it is incoming or outgoing. For example:

My username is 'admin'.

USER   |   MESSAGE       |   DATE   |  DIRECTION
Dan    | Hello           |  1/2/10  | Incoming
Bob    | How are you Bob?|  30/1/10 | Outgoing

The problem is not repeating the username because of differing directions. For example:

USER   |   MESSAGE       |   DATE   |  DIRECTION
Dan    | Hello           |  1/2/10  | Incoming
Bob    | How are you Bob?|  30/1/10 | Outgoing
Bob    | Hi admin        |  30/1/10 | Incoming

So I need to make sure that the only message that shows is the newest communication irrespective of the direction (i.e., the newest message with another user, irrespective of whether that user is the 'sender' or 'recipient').

I'm using PHP/MYSQL for this website. I've tried thousands of ways to do this but I just can't figure it out. It might be that my database is incorrectly structured. I will be online for a few hours (and will continually check after that) so feel free to ask any questions.

Can you show an example of what you've tried? Also am I correct in saying you looking for the newest message which a user has either sent or received? — Jim, Jan 09 '13 at 03:04
Yes, I am looking for the newest message that the user has either send or received. I have tried: `SELECT i.correspondent, m.subject, i.msg FROM ( SELECT sender AS correspondent, MAX(message) msg FROM mesages WHERE receiver='Daniel' GROUP BY sender UNION SELECT receiver AS correspondent, MAX(message) FROM mesages WHERE sender='Daniel' GROUP BY receiver )i, messages m WHERE i.msg=m.message;` — Daniel, Jan 09 '13 at 03:08
`(SELECT Receiver, Subject, Message ,'Sender' as 'type' FROM m WHERE Sender='Daniel' ORDER By message desc limit 1) UNION ALL (SELECT Sender, Subject, Message ,'Reciever' as 'type' FROM m WHERE Receiver='Daniel'ORDER By message desc limit 1)` — Daniel, Jan 09 '13 at 03:09
Now I am thinking of something like this: `$query = "SELECT * FROM messages WHERE sender = '$user'"; $outgoing = mysql_query($query) or die(mysql_error()); $query = "SELECT * FROM messages WHERE reciever = '$user'"; $incoming = mysql_query($query) or die(mysql_error()); while($outgoing = mysql_fetch_array($outgoing) && $incoming = mysql_fetch_array($incoming)){ ` Then see which direction is newer? I don't know! — Daniel, Jan 09 '13 at 03:10

Jim · Answer 1 · 2013-01-09T04:13:18.997

1

If I understand correctly this should do what you need:

SELECT
IF(senderid = :userId,recipient,sender) AS USER,
message,
date,
IF(senderid = :userId,'Outgoing','Incoming') AS DIRECTION
FROM message 
WHERE senderid = :userId OR recipientid = :userId 
ORDER BY date DESC

The USER and DIRECTION is determined based on whether this user is the sender or not.

If you are only interested in the latest message you can add a LIMIT to this query.

edited Jan 09 '13 at 04:13

answered Jan 09 '13 at 03:08

Jim

22,354
6
52
80

Thanks Jim! I'm have a little trouble with "Outgoing","Incoming", I get an undefined constant/unexpected error. Could you help me out? – Daniel Jan 09 '13 at 03:23
Oh I see! There are no "Outgoing" or "Incoming" values in the database. That is determined by whether the user is the sender or recipient. Would that be a problem? – Daniel Jan 09 '13 at 03:28
@user1912319 Yeah. You should replace :userId with the id of the user you are getting messages for. – Jim Jan 09 '13 at 03:29
I've never seen a query like this before. I have changed userId to the user's id but I'm still getting the same ` unexpected 'Outgoing'` error. I have a feeling it is because neither "Outgoing" nor "Incoming" are defined anywhere in my table. – Daniel Jan 09 '13 at 03:40
Can you post the code you are using and the full error message? – Jim Jan 09 '13 at 03:42
`$query = "SELECT IF(senderid = '$userid'',reciever,sender) AS USER, message, date, IF(senderid = '$userid',"Outgoing","Incoming") AS DIRECTION, FROM message WHERE senderid = '$userid' OR recieverid = '$userid' ORDER BY date DESC";` Ignore the changes in columns and the typos. They are all correct. – Daniel Jan 09 '13 at 03:47
The error message is: `Parse error: syntax error, unexpected 'Outgoing' (T_STRING) in C:\xampp\htdocs\final\messages.php on line 16` – Daniel Jan 09 '13 at 03:47
@user1912319 The string you posted is enclosed in `"` and the SQL has `"` inside it, which is causing PHP to try and find outgoing. Try replacing `"` around incoming and outgoing with `'`. I'll update the question. – Jim Jan 09 '13 at 03:50
That fixed the previous error. Now I'm getting `You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '4','Outgoing','Incoming') AS DIRECTION, FROM me' at line 2` Note that '4' refers to the user's id. Sorry this must be getting frustrating! – Daniel Jan 09 '13 at 03:55
It looks like there may be an extra `'` after the first user id. Not frustrating, I'm happy to help. – Jim Jan 09 '13 at 03:58
`$query = "SELECT IF(senderid = '$userid',reciever,sender) AS USER, messages, date, IF(senderid = '$userid','Outgoing','Incoming') AS DIRECTION, FROM messages WHERE senderid = '$userid' OR recieverid = '$userid' ORDER BY date DESC";` Still gives me: `You have an error in your SQL syntax... near 'FROM messages WHERE senderid = '4' OR recieveri' at line 6` – Daniel Jan 09 '13 at 04:08
@user1912319 Bah. Mistake in my code. There's a comma after DIRECTION which shouldn't be there. Will update the answer. – Jim Jan 09 '13 at 04:12
It's still showing all the messages :( Not to worry, thanks for your help! – Daniel Jan 09 '13 at 04:21
@user1912319 If you only wish to see one add "LIMIT 1" to the end of the query. – Jim Jan 09 '13 at 20:09

score 0 · Accepted Answer · answered Jan 09 '13 at 07:34

I have somewhat solved my own question with the following:

SELECT * FROM (
    SELECT * FROM (
         (SELECT id, reciever, recieverid, message, datetime FROM messages WHERE sender = '$user')
       UNION
          (SELECT id, sender, senderid, message, datetime FROM messages WHERE reciever = '$user')
          ) as t
       ORDER BY datetime DESC
    ) as t2
GROUP BY reciever ORDER BY datetime DESC

It seems to do the trick, although I don't know which messages are incoming or outgoing. I'll know if it works properly when I have a chance to invite some actual users.

PHP/MYSQL - showing newest distinct value from multiple columns

2 Answers2