我正在使用codeigniter为我的网站构建一个私人消息应用程序。一切正常,但我在获取收件箱对话时遇到了一个大问题。当用户A向用户B发送消息时,用户B能够接收该消息;它获取消息细节和用户包括化身和用户名的细节,但当用户C也向用户B发送消息时。每条消息(包括用户A的详细信息,如头像和姓名(消息和主题没有))都会更改为用户C,使他们成为原始消息的发送者。我不确定出了什么问题。下面是我的数据库结构和代码。
conversation table
----------------------------------------------------
conversation_id | conversation_subject
____________________________________________________
18 | hello there.
19 | Chelsea is winning tomorrow
___________________________________________________
conversations members table
---------------------------------------------------------------------------
conversation_id | user_id | conversation_last_view | conversation_deleted
___________________________________________________________________________
18 | 1 | 0 | 0
18 | 11 | 0 | 0
19 | 1 | 0 | 0
19 | 13 | 0 | 0
___________________________________________________________________________
conversations messages
----------------------------------------------------------------------------------------------------------------------
message_id |conversation_id | user_id | message_date | message_text
______________________________________________________________________________________________________________________
18 | 1 | 1 | 2016-05-03 20:06:53 | I just want you to know that , you shaa
18 | 11 | 13 | 2016-05-03 20:23:53 | hello guy
_______________________________________________________________________________________________________________________
控制器=
public function inbox() {
$user['user'] = $this->data;
// store's logged in user's id
$user_id = $user['user'][0]->id;
$taxi['conversation'] = $this->conversation_model->fetch_messages($user_id);
if (!empty($taxi['conversation'])) {
$conversation_id = $taxi['conversation'] [0]['conversation_id'];
$conversation['inbox'] = $this->conversation_model->fetch_inbox($conversation_id,$user_id);
//fetches member of the conversation
$member_id = $taxi2['inbox'] [0]-> user_id;
// Fetching message member that will later appear as the sender on the inbox page.
$message_member['sender'] = $this->conversation_model->fetch_member($member_id);
// merge all the data
$inbox = array_merge($user,$conversation,$message_member);
$this->load->view('template/inbox',$inbox);
}
else {
$this->load->view('template/no_message');
}
}
模型
public function fetch_member($member_id)
{
$q = $this->db->get_where('gamers', array('id' => $member_id));
if ($q->num_rows > 0) {
return $q->result();
}
return false;
}
public function fetch_inbox($conversation_id,$sender_id)
{
$query = $this->db->query( "select conversations_members.conversation_id,conversations_members.user_id,conversations_members.conversation_last_view,'n"
. "conversations_members.conversation_deleted,conversations_messages.message_id,conversations_messages.message_date,conversations_messages.message_text'n"
. "'n"
. "from conversations_members'n"
. "'n"
. "'n"
. "inner join conversations on conversations_members.conversation_id = conversations.conversation_id
inner join conversations_messages on conversations.conversation_id = conversations_members.conversation_id
where conversations.conversation_id = $conversation_id and conversations_members.conversation_deleted = 0 and conversations_members.user_id <> $sender_id" );
$results= $query->result();
return $results;
}
public function fetch_messages($sender_id)
{
$this->db->select('conversations.conversation_id,conversations.conversation_subject,conversations_messages.message_text');
$this->db->select_max('conversations_messages.message_date', 'conversation_last_reply');
$this->db->select_max('conversations_messages.message_date > conversations_members.conversation_last_view', 'conversation_unread');
$this->db->from('conversations');
$this->db->join('conversations_messages','conversations.conversation_id=conversations_messages.conversation_id','left');
$this->db->join('conversations_members','conversations.conversation_id= conversations_members.conversation_id','inner');
$this->db->where('conversations_members.user_id',$sender_id);
$this->db->where('conversations_members.conversation_deleted','0');
$this->db->group_by('conversations.conversation_id');
$this->db->order_by('conversation_last_reply', 'desc');
// Get the results from db
$query = $this->db->get();
// Result array contains the records selected
$result_array = $query->result_array();
return $result_array;
}
更新:我认为问题来自这一部分
$taxi['conversation'] = $this->conversation_model->fetch_messages($user_id);
if (!empty($taxi['conversation'])) {
$conversation_id = $taxi['conversation'] [0]['conversation_id'];
它返回一组会话详细信息,如消息、文本和会话成员的id。像低于
Array ( [conversation] => Array ( [0] => Array ( [conversation_id] => 25 [conversation_subject] => helloooooooo guy [message_text] => hello [conversation_last_reply] => 2016-05-08 02:59:06 [conversation_unread] => 1 ) [1] => Array ( [conversation_id] => 23 [conversation_subject] => this is new [message_text] => helllo [conversation_last_reply] => 2016-05-03 20:06:53 [conversation_unread] => 1 ) ) )
我假设从数组中选择每个conversation_id。但想象一下,如果有大约400个对话可以显示。如何从数组中获取所有conversationid,然后将它们存储到另一个数组中,以便使用它从400条消息中获取每个会话成员?听起来有点不可能。有其他方法可以让事情变得简单吗?
我通过在消息表中添加from_user_avatar和to_user_avatar等列来解决问题。。我对用户名等也做了同样的操作,它就像一个符咒。