I could use some help with my SQL command

I've got a database table called 'mesg' with the following structure:

receiver_id | sender_id | message | timestamp | read

Example:

2 *(«me)* | 6 *(«nice girl)* | 'I like you, more than ghoti' | yearsago | 1 *(«seen it)*
2 *(«me)* | 6 *(«nice girl)* | 'I like you, more than fish' | now | 1 *(«seen it)*
6 *(«nice girl)* | 2 *(«me)* | 'Hey, wanna go fish?' | yearsago+1sec | 0 *(«she hasn't seen it)*

It's quite a tricky thing that I want to achieve.

I want to get: the most recent message(=ORDER BY time DESC) + 'contact name' + time for each 'conversation'.

• Contact name = uname WHERE uid = 'contact ID' (the username is in another table)
• Contact ID = if(sessionID*(«me)*=sender_id){receiver_id}else{sender_id}
• Conversation is me = receiver OR me = sender

For example:

From: **Bas Kreuntjes** *(« The message from bas is the most recent)*
    Hey $py, How are you doing...
From: **Sophie Naarden** *(« Second recent)*
    Well hello, would you like to buy my spam? ... *(«I'll work on that later >p)*
To:    **Melanie van Deijk** *(« My message to Melanie is 3th)*
    And? Did you kiss him? ...

That is a rough output.

QUESTION: Could someone please help me setup a good SQL command. This will be the while loup

<?php
$sql = "????";
$result = mysql_query($sql);
while($fetch = mysql_fetch_assoc($result)){ ?>
    <div class="message-block">
        <h1><?php echo($fetch['uname']); ?></h1>
        <p><?php echo($fetch['message']); ?></p>
        <p><?php echo($fetch['time']); ?></p>
    </div>
<?p开发者_如何学运维hp } ?>

I hope my explanation is good enough, if not, please tell me. Please don't mind my English, and the Dutch names (I am Dutch myself) Feel free to correct my English

UPDATE1: Best I've got until now: But I do not want more than one conversation to show up... u=user table m=message table

SELECT u.uname, m.message, m.receiver_uid, m.sender_uid, m.time 
FROM    m, u
WHERE   (m.receiver_uid = '$myID' AND u.uid = m.sender_uid)
OR      (m.sender_uid = '$myID' AND u.uid = m.receiver_uid)
ORDER BY    time DESC;

---

Fiddly...but doable. Build the answer up one piece at a time.

Constructing the query - step by step

Given a particular reference user ('me') in the examples, you need to find all messages between 'me' and another person. For each such other person, you want to find the one message with the most recent time stamp.

Query 1

Let's get a conversation ID established, linking all messages together. Since we're interested in one party ('me') at a time, we can use the other person's ID to identify the conversation.

SELECT m.recipient_uid AS conversation,
       m.recipient_uid AS recipient_uid,
       m.sender_uid    AS sender_uid,
       m.timestamp     AS timestamp
  FROM mesg AS m
 WHERE m.sender_uid = '$myID'
UNION
SELECT m.sender_uid    AS conversation,
       m.recipient_uid AS recipient_uid,
       m.sender_uid    AS sender_uid,
       m.timestamp     AS timestamp
  FROM mesg AS m
 WHERE m.recipient_uid = '$myID'

That allows you to thread all the chats with an individual together with the same conversation ID.

Query 2

Now you need to group that by conversation to find the conversation and the most recent time; for this, you don't need the other (recipient_uid or sender_uid) columns:

SELECT conversation, MAX(timestamp) AS max_timestamp
  FROM (SELECT m.recipient_uid AS conversation,
               m.timestamp     AS timestamp
          FROM mesg AS m
         WHERE m.sender_uid = '$myID'
        UNION
        SELECT m.sender_uid    AS conversation,
               m.timestamp     AS timestamp
          FROM mesg AS m
         WHERE m.recipient_uid = '$myID') AS c
  GROUP BY conversation

Query 3

So, for each conversation, we now know the most recent timestamp. We just have to join that information back with the previous query to get the majority of the details:

SELECT c.recipient_uid, c.sender_uid, c.timestamp
  FROM (SELECT conversation, MAX(timestamp) AS max_timestamp
          FROM (SELECT m.recipient_uid AS conversation,
                       m.timestamp     AS timestamp
                  FROM mesg AS m
                 WHERE m.sender_uid = '$myID'
                UNION
                SELECT m.sender_uid    AS conversation,
                       m.timestamp     AS timestamp
                  FROM mesg AS m
                 WHERE m.recipient_uid = '$myID') AS c
          GROUP BY conversation
       ) AS x
  JOIN (SELECT m.recipient_uid AS conversation,
               m.recipient_uid AS recipient_uid,
               m.sender_uid    AS sender_uid,
               m.timestamp     AS timestamp
          FROM mesg AS m
         WHERE m.sender_uid = '$myID'
        UNION
        SELECT m.sender_uid    AS conversation,
               m.recipient_uid AS recipient_uid,
               m.sender_uid    AS sender_uid,
               m.timestamp     AS timestamp
          FROM mesg AS m
         WHERE m.recipient_uid = '$myID'
       ) AS c
    ON c.conversation = x.conversation AND c.timestamp = x.max_timestamp

Query 4

Since you said you wanted names as well, you can extend that to join with the users table twice:

SELECT c.recipient_uid, c.sender_uid, c.timestamp,
       u1.uname AS recipient, u2.uname AS sender
  FROM (SELECT conversation, MAX(timestamp) AS max_timestamp
          FROM (SELECT m.recipient_uid AS conversation,
                       m.timestamp     AS timestamp
                  FROM mesg AS m
                 WHERE m.sender_uid = '$myID'
                UNION
                SELECT m.sender_uid    AS conversation,
                       m.timestamp     AS timestamp
                  FROM mesg AS m
                 WHERE m.recipient_uid = '$myID') AS c
          GROUP BY conversation
       ) AS x
  JOIN (SELECT m.recipient_uid AS conversation,
               m.recipient_uid AS recipient_uid,
               m.sender_uid    AS sender_uid,
               m.timestamp     AS timestamp
          FROM mesg AS m
         WHERE m.sender_uid = '$myID'
        UNION
        SELECT m.sender_uid    AS conversation,
               m.recipient_uid AS recipient_uid,
               m.sender_uid    AS sender_uid,
               m.timestamp     AS timestamp
          FROM mesg AS m
         WHERE m.recipient_uid = '$myID'
       ) AS c
    ON c.conversation = x.conversation AND c.timestamp = x.max_timestamp
  JOIN user AS u1
    ON u1.uid = c.recipient_uid
  JOIN user AS u2
    ON u2.uid = c.sender_uid

There - that's fun. I wouldn't want to write that in one go, but built up a piece at a time, it isn't too intimidating (though it isn't trivial).

Test Schema

User table

CREATE TABLE user
(
    uid     INTEGER NOT NULL PRIMARY KEY,
    uname   VARCHAR(30) NOT NULL
);

Mesg Table

DATETIME YEAR TO SECOND is a funny way of writing TIMESTAMP (in IBM Informix Dynamic Server - which is where I tested this).

CREATE TABLE mesg
(
    recipient_uid   INTEGER NOT NULL REFERENCES user(uid),
    sender_uid      INTEGER NOT NULL REFERENCES user(uid),
    message         VARCHAR(255) NOT NULL,
    timestamp       DATETIME YEAR TO SECOND NOT NULL,
    PRIMARY KEY (recipient_uid, sender_uid, timestamp),
    READ            CHAR(1) NOT NULL
);

User Data

INSERT INTO USER VALUES(2, 'My Full Name');
INSERT INTO USER VALUES(6, 'Her Full Name');
INSERT INTO USER VALUES(3, 'Dag Brunner');

Mesg Data

INSERT INTO mesg VALUES(2, 6, 'I like you, more than ghoti', '2008-01-01 00:05:03', 1);
INSERT INTO mesg VALUES(2, 6, 'I like you, more than fish',  '2011-01-15 13:45:09', 1);
INSERT INTO mesg VALUES(6, 2, 'Hey, wanna go fish?',         '2008-01-01 09:30:47', 0);
INSERT INTO mesg VALUES(2, 3, 'Wanna catch a beer?',  '2011-01-14 13:45:09', 1);
INSERT INTO mesg VALUES(3, 2, 'Sounds good to me!!',  '2011-01-14 13:55:39', 1);
INSERT INTO mesg VALUES(3, 6, 'Heading home now???',  '2010-12-31 12:27:41', 1);
INSERT INTO mesg VALUES(6, 3, 'Yes - on the bus!!!',  '2010-12-31 13:55:39', 1);

Results 1

Conv    Recv    Send    When
3       2       3       2011-01-14 13:45:09
3       3       2       2011-01-14 13:55:39
6       2       6       2008-01-01 00:05:03
6       2       6       2011-01-15 13:45:09
6       6       2       2008-01-01 09:30:47

Results 2

Conv    Most Recent
3       2011-01-14 13:55:39
6       2011-01-15 13:45:09

Results 3

Recv    Send    When
3       2       2011-01-14 13:55:39
2       6       2011-01-15 13:45:09

Results 4

Recv    Send    When                    Receiver        Sender
3       2       2011-01-14 13:55:39     Dag Brunner     My Full Name
2       6       2011-01-15 13:45:09     My Full Name    Her Full Name

Wow, it isn't often I get a piece of SQL that is this complex right on the first go, but on this occasion, apart from a trip up over receiver_uid vs recipient_uid in the first iteration, it did work correctly first time.

---

General Solution

Omitting the 'who' (or 'me', or '$myID') parameter, we can come up with a general solution for the latest message in any of the conversations between two people. The conversation is identified by the higher and lower (or vice versa) participant ID. Otherwise, it is very similar to the previous one.

SELECT c.recipient_uid, c.sender_uid, c.timestamp,
       u1.uname AS recipient, u2.uname AS sender
  FROM (SELECT conv01, conv02, MAX(timestamp) AS max_timestamp
          FROM (SELECT m.recipient_uid AS conv01,
                       m.sender_uid    AS conv02,
                       m.timestamp     AS timestamp
                  FROM mesg AS m
                 WHERE m.sender_uid < m.recipient_uid
                UNION
                SELECT m.sender_uid    AS conv01,
                       m.recipient_uid AS conv02,
                       m.timestamp     AS timestamp
                  FROM mesg AS m
                 WHERE m.sender_uid > m.recipient_uid
               ) AS C
          GROUP BY conv01, conv02
       ) AS x
  JOIN (SELECT m.recipient_uid AS conv01,
               m.sender_uid    AS conv02,
               m.recipient_uid AS recipient_uid,
               m.sender_uid    AS sender_uid,
               m.timestamp     AS timestamp
          FROM mesg AS m
         WHERE m.sender_uid < m.recipient_uid
        UNION
        SELECT m.sender_uid    AS conv01,
               m.recipient_uid AS conv02,
               m.recipient_uid AS recipient_uid,
               m.sender_uid    AS sender_uid,
               m.timestamp     AS timestamp
          FROM mesg AS m
         WHERE m.sender_uid > m.recipient_uid
       ) AS C
    ON c.conv01 = x.conv01 AND c.conv02 = x.conv02
   AND c.timestamp = x.max_timestamp
  JOIN USER AS u1
    ON u1.uid = C.recipient_uid
  JOIN USER AS u2
    ON u2.uid = C.sender_uid;

Result with sample data

Recv    Send    When                    Recipient       Sender
6       3       2010-12-31 13:55:39     Her Full Name   Dag Brunner
2       6       2011-01-15 13:45:09     My Full Name    Her Full Name
3       2       2011-01-14 13:55:39     Dag Brunner     My Full Name

---

It's quite a tricky thing that I want to achieve.

Not really.

You can get the time of the last message simply using:

SELECT receiver_id, sender_id, MAX(timestamp) as mtime 
FROM mesg
GROUP BY receiver_id, sender_id

(You probably want to add a where class to filter by sender / recipient) And then to get the actual message....

SELECT m2.*
FROM mesg m2,
(SELECT receiver_id, sender_id, MAX(timestamp) as mtime 
FROM mesg
GROUP BY receiver_id, sender_id) ilv
WHERE m2.sender_id=ilv.seder_id
AND m2.receiver_id=ilv.receiver_id
AND m2.timestamp=ilv.mtime;

This is however rather innefficient as it opens 2 cursors on the table, a more efficient solution (and if you're running a running a very old version of mysql, the only solution) is to use the max concat trick.

I'll leave that as an exercise for you to work out.

---

I've perfected the script Jonathan: Not true (UPDATE)

The fist step works:

SELECT message, receiver_uid AS receiver, sender_uid AS sender, time, sender_uid AS me, receiver_uid AS contact 
    FROM messages 
    WHERE sender_uid = '$me' ) 
    UNION 
  ( SELECT message, receiver_uid AS receiver, sender_uid AS sender, time, receiver_uid AS me, sender_uid AS contact 
    FROM messages 
    WHERE receiver_uid = '$me' )
    ORDER BY time DESC

It gives a nice clean list which tells me if i am the sender or not and who my contact is. But the combination in the second part [MAX() and GROUP BY] doesn't work they way i want it to.

It does not select the most recent conversation piece (send or receive shouldn't matter). It rather just picks the first new ID ot encounters. So MAX() doesnt do its job.

SELECT m.message, m.receiver, m.sender, max(m.time) « doesn't work well... AS time, m.me, m.contact, u.ufirstname
    FROM( ( SELECT message, receiver_uid AS receiver, sender_uid AS sender, time, sender_uid AS me, receiver_uid AS contact 
            FROM messages 
            WHERE sender_uid = '$me' ) 
            UNION 
          ( SELECT message, receiver_uid AS receiver, sender_uid AS sender, time, receiver_uid AS me, sender_uid AS contact 
            FROM messages 
            WHERE receiver_uid = '$me' )
            ORDER BY time DESC) AS m, users AS u
    WHERE u.uid = m.contact
    GROUP BY m.contact «This one does not do what we want it to do :(
    ORDER BY time DESC;

---

Which format does THE time in THE database need to use THE function MAX() on it. Max() does not select THE most recent date, that is what is going wrong.