I could use some help with my SQL command

Question

I have a database table called "mesg" with the following structure:

receiver_id | sender_id | message | time stamp | 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)* 

This is a pretty complicated thing that I want to achieve.

I want to receive: last message (= ORDER BY time DESC) + "contact name" + time for each "conversation".

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

For instance:

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

This is a rough conclusion.

QUESTION: Can someone help me set up a good SQL command. It will be 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> <?php } ?> 

I hope my explanation will be good enough, if not, please tell me. Please ignore my English and Dutch names (I'm Dutch myself) Feel free to correct my English

UPDATE1: The best I have so far: But I do not want more than one conversation to appear ... 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; 

Answer 1

Scanty ... but doable. Build the answer one piece at a time.

Building a query - step by step

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

Request 1

Allows you to set the conversation ID by linking all messages together. Since we are interested in one side (“I”) at a time, we can use the identifier of the other person 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' 

This allows you to scroll through all the chats along with the same conversation id.

Request 2

Now you need to group this by conversation in order to find the conversation and most recently; for this you do not need other columns (recipient_uid or sender_uid):

 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 

Request 3

So, for every conversation, we now know the latest timestamp. We just need to join this information with a previous request to get most 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 

Request 4

Since you said you want to get names, you can expand this to join the user 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 

It is fun there. I would not want to write this at a time, but created a piece at a time, it is not too intimidating (although this is not trivial).

Testing scheme

User table

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

Mesg table

DATETIME YEAR TO SECOND is a fun way to write TIMESTAMP (in IBM Informix Dynamic Server - 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, I don’t often get a piece of SQL that is this complicated right the first time, but in this case, in addition to riding the receiver_uid vs recipient_uid method on the first iteration, it really worked right the first time.

---

Common decision

Omitting the “who” parameter (or “me” or “$ myID”), we can find a common solution for the last message in any of the conversations between two people. The conversation is determined by the identifier of the participant with a higher and lower (or vice versa). 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 

Answer 2

This is a pretty complicated thing that I want to achieve.

Not really.

You can get the time of the last message simply by 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 the sender / receiver) 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, however, is pretty inefficient as it opens 2 cursors on the table, a more efficient solution (and if you are using a very old version of mysql, the only solution) is to use max concat trick .

I will leave this an exercise for you.

Answer 3

I perfected the Jonathan script: not true (UPDATE)

The first step is performed:

 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 that tells me whether I am a sender or not, and who is my contact. But the combination in the second part of [MAX () and GROUP BY] does not work the way I want.

He does not select the very last part of the conversation (sending or receiving should not matter). Rather, he simply picks the first new ID meetings. Therefore, MAX () does not 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; 

Answer 4

In what format should the time in the database use the MAX () function. Max () does not select the most recent date, this is what happens wrong.