CODEWITHSUNDEEP
phpmysqlsqlmultidimensional-array
Refilon
Refilon

Reputation: 3499

Get chat data and sort by receiver and sender id

I've got a question.

I've got a database like this:

messages_id | int | Auto-increment
from | int
to | int
message | text

Now I have problems with grouping them by sender ID. I only want to retrieve the messages that are send or received by the user that is logged in. That's not too hard.

SELECT * FROM messages WHERE from = 1 OR to = 1 ORDER BY messages_id ASC

But now, they are not grouped. As different people can message this user. I do not really know where to start.

I want something like this:

array(
    [5] => array(
        [0] => "message One",
        [1] => "Message two"
    ),
    [32] => array(
        [0] => "message One",
        [1] => "Message two"
    )
);

The 5 and 32 are the ID's of the people who's been chatting with.

Hope you guys can help :)

Upvotes: 0

Views: 781

Answers (3)

Refilon
Refilon

Reputation: 3499

Thanks for all reply's. Really apreciate it, but I figured it out myself already ;)

Now I got the following:

$currentUserID = get_current_user_id();
$rows = $wpdb->get_results("SELECT * FROM `messages` WHERE `from` = '" . $currentUserID . "' OR `to` = '" . currentUserID . "' ORDER BY `messages_id` ASC");

$messages = [];

foreach($rows as $row) {
    if($row->from == $currentUserID) {
        $messages[$row->to][] .= $row->message;
    }
    else {
        $messages[$row->from][] .= $row->message;
    }
}
print_r($messages);

Upvotes: 1

Dipanwita Kundu
Dipanwita Kundu

Reputation: 1667

Try this: (Assuming each field must have eitherAuto-increment_from or to. And for received case to field has value 

SELECT *, if(`Auto-increment_from` = 1 , 'From' , 'From') as meaage_type 
FROM messages WHERE from = 1 OR to = 1 ORDER BY messages_id ASC

Upvotes: 0

JohnHC
JohnHC

Reputation: 11205

I had a problem like this a few years back. I did this:

select 'In' as MessageDirection, `From` as Contact, `To` as UserId, Message 
from Messages
where `To` = 1
union
select 'Out' as MessageDirection, `To` as Contact, `From` as UserId, Message 
from Messages
where `From` = 1
order by Contact

Upvotes: 0

Related Questions