Group all from and to messages per user

Question

I was wondering if someone could help me out. I am working on a Private Message center which will make users able to send PM's to each other through front end. The inboxes are working perfectly fine, however I would like to add a page where you are able to click on a users name and then you can see all messages send to and from with this user and the current logged in user.

For example:

User 1
Message A
Message B
Message C
Message D

User 2
Message A
Message B
Message C

User 3
Message A
Message B
Message C
Message D
Message E

In my database I have a table with all users:

id | user_login | user_pass | user_nicename | user_email | user_url | user_registered | user_activation_key

And I have a table with info of the messages:

id | parent_id | from_user | to_user | send_date | last_date | message_title | message_contents | status | to_del | from_del

The user id in table 1 corresponds with parent_id (nested messages in form of replies) and the from_user and to_user in table 2.

With the next query (which I used after I found the basis here on Stackoverflow), I am able to echo the results from the from_user column, however I would like to echo all messages send from and to a user:

global $wpdb;
$result = $wpdb->get_results( "SELECT table1.id, table1.user_nicename, table2.id,
table2.from_user, table2.message_title FROM table1 
INNER JOIN table2 ON table1.id=table2.from_user 
GROUP BY user_nicename"  ); 

foreach($result as $row)
{
echo $row->user_nicename." <br>  ".$row->message_title."<br>";
}
?>

Could anyone perhaps shed a light on it and point me into the right direction? I think I need to use something like INNER JOIN to add the to_user to the query, however I get a bit lost in it.

In case you need more information, just let me know. First time turning for help here, so I don't know if I have provided enough info :)

Thank you very much in advance for your attention and help!

Answer 1

You can use group_concat function for doing so.

Select table1.id, table1.user_nicename, 
       group_concat(table2.message_title SEPARATOR "|") as Message
FROM table1 
LEFT JOIN table2 
ON table1.id=table2.parent_id 
GROUP BY table2.parent_id

You will have the different messages concated by | Use PHP array explode to separate the messages.

Answer 2

Try this:

SELECT t1.id, t1.user_nicename, t2.id, t2.from_user, t2.message_title 
FROM table1 t1
INNER JOIN table2 t2 ON t1.id IN (t2.from_user, t2.to_user)
ORDER BY t1.id;

If you want messages of specific user then use below query:

SELECT t1.id, t1.user_nicename, t2.id, t2.from_user, t2.message_title 
FROM table1 t1
INNER JOIN table2 t2 ON t1.id IN (t2.from_user, t2.to_user)
WHERE t1.id = 1;