Group all messages and for messages per user
I was wondering if anyone can help me. I am working on a Private Message Center that will allow users to send PM to each other through an interface. The inbox works fine, however I would like to add a page where you can click the username and then you can see all messages sent with that user and the currently 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 posts information:
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 to parent_id (message attachments in the form of responses) and from_user and to_user in Table 2.
With the following query (which I used after I found the base here on Stackoverflow), I can repeat the results from the from_user column, however, I would like all messages to be sent from user and 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>";
}
?>
Can anyone shed some light on it and point me in the right direction? I think I need to use something like INNER JOIN to add to_user to the request, however I lost it a bit.
If you need more information, just let me know. First time asking for help here so I don't know if I have provided enough information :)
Thank you for your attention and help!
source to share
Try the following:
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 need posts from a specific user, use the query below:
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;
source to share
You can use the group_concat function for this.
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 different messages decorated with | Use PHP array to separate messages.
source to share