So I now have added a conversation table, which all mail rows reference to(via the id key in the conversation table). I have a further question with this setup. I also have a table called Member_Profile(which holds first and last names of users). I need to get the full name for both the from_user and the to_user. I have tried using an alias table and it does not return the expected results. So again, what I am asking is to retrieve all messages the current user is a part of(from the mail table), and then get the current user's full name, and the other party's full name. The query in its simplest form:
$result = mysql_query("SELECT tbl1.to_name,tbl2.from_name,Mail.message FROM (SELECT CONCAT(Member_Profile.first_name,Member_Profile.last_name)AS to_name FROM Member_Profile,Mail WHERE Mail.to_user='$_SESSION[user_id]' AND Member_Profile.user_id=Mail.to_user)AS tbl1,(SELECT CONCAT(Member_Profile.first_name,Member_Profile.last_name)AS from_name FROM Member_Profile,Mail WHERE Conversations.from_user='$_SESSION[user_id]' AND Member_Profile.user_id=Mail.to_user) AS tbl2")or die(mysql_error());
It is certainly simple, but I have a hard time with this.