CODEWITHSUNDEEP
phpmysqlkohana
Vitor Deco
Vitor Deco

Reputation: 133

Messaging System - MySql / PHP / Kohana

I am developing a messaging system using the following structure in the database.

I took the diagram here on the site

The question is how to select all messages in PHP grouping by user.

EX:

-All messages from user A to user X

-All messages from user B to user X

-All messages from user C to user X

(user X = user logged)

Upvotes: 1

Views: 439

Answers (2)

Asenar
Asenar

Reputation: 7030

If all required informations are in tables, here is a partial solution that uses the database module of kohana3. If the following doesn't fit your schema, you will easily adapt it.

$user_id = '1'; // whatever how you get the current user
$messages = DB::select('*')->from('messages')
  ->join('users_has_messages', 'LEFT')->on('messages_id', '=', 'id')
  ->where('users_id', '=', $user_id)
  ->order_by('from_id ASC')
  ->execute()->as_array();
foreach($messages as $msg)
{
  echo $msg->id.':'.$msg->subject.'<br/>';
}

Upvotes: 1

Damien Black
Damien Black

Reputation: 5647

You could easily grab all messages with a MySQL query like this:

SELECT messages.* FROM users
JOIN users_has_messages ON users.id = users_has_messages.users_id
JOIN messages ON users_has_messages.messages_id = messages.id
WHERE users.id = __THE ID OF THE USER__

Just replace __THE ID OF THE USER__ with the id of the user you are interested in getting messages from.

The way this query works is by 'joining' the tables together 'on' certain conditions. First we join the users table to the users_has_messages table when users.id = users_has_messages.users_id then we do a similar 'join' to the messages table.

Finally you use the WHERE statement to define conditions. In this case, we only want the users with a specific id.

After looking at your last edit, it seems you want to get all the messages from a particular user as well. Unfortunately, your schema doesn't have the information of who sent the message. If you want that, you will have to add the send_user_id to the messages table. Then you could do this:

SELECT messages.* FROM users
JOIN users_has_messages ON users.id = users_has_messages.users_id
JOIN messages ON users_has_messages.messages_id = messages.id
WHERE users.id = __THE ID OF THE RECIEVER__
AND messages.send_user_id = __THE ID OF THE SENDER__

Upvotes: 1

Related Questions