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.
加载中,请稍侯......
精彩评论