php mysql concatenate sent and received messages

Private Messaging system displaying users messages concatenated.

• Users sent and received messages grouped together ordered by time_sent

• each message tab will display the sender/receiver's name, message, and time_sent

• any tips on how to improve query speed(such as indexes,etc.)?

MESSAGES TABLE 
--------------
id sender receiver time_sent message opened recipientDelete senderDelete

Users Table
-----------
id first_name last_name

My attempt:

$userid = logged in user
$query="SELECT MAX(id), sender, receiver, MIN(time_sent), message, opened 
        FROM messages 
        WHERE (receiver='$userid' AND recipientDelete='0') 
             OR (sender='$userid' AND senderDelete='0') 
        GROUP BY receiver,sender 
        ORDER BY time_sent DESC LIMIT 8";

RESULT/Problem:

if the logged in user (id=3) receives messages from two users (id=1,and id=2) this query will return messages sent from users 1 and 2

Second attempt:

 $query="SELECT MAX(id), sender, receiver, MIN(time_sent), message, opened      
     FROM ( SELECT 
              CASE WHEN sender = '$userid' THEN receiver ELSE sender END
              MIN(time_sent)
            FROM messages 
            WHERE sender = '$userid' OR receiver = '$userid' 
            GROUP BY CAS开发者_如何学PythonE WHEN sender = '$userid' THEN receiver ELSE sender END)
         ORDER BY time_sent DESC 
         LIMIT 8";

Returned an error.

---

You're selecting from a derived table. In MySQL, that sub-query must be aliased:

SELECT ...
FROM ( SELECT ...) AS subqueryalias
                   ^^^^^^^^^^^^^^^^
WHERE ...