我正在开发一个私人消息系统,我有3个表,看起来像这个
对话对话_id|对话_主题
CONVERSATIONS_EMBERSconversation_id | user_id | conversation_last_view | conversion_deleted
CONVERSATIONS_MESSAGESmessage_id | conversation_id | user_id | message_date | message_text
表格结构不言自明。但我想要的是加入所有的表格,选择并在收件箱页面上显示邮件。
我尝试过这个查询,但总是出错。我是sql的新手;我只能做简单的事。
$this->db->select('conversations, conversation_id,conversations, conversation_subject');
$this->db->select_max('conversations_messages.message_date', 'conversation_last_reply');
$this->db->from('conversations');
$this->db->join('conversations_messages','conversations.id=conversations_messages.conversation_id','left');
$this->db->join('conversations_members','conversations.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');
// Get the results from db
$query = $this->db->get();
// Result array contains the records selected
$result_array = $query->result_array();
您的选择方法中有一个错误。问题是您正在使用(,)用于绑定,应与(.)一起使用
修改的查询:
$this->db->select('conversations.conversation_id,conversations.conversation_subject');
我所改变的:
替换此:
'conversations, conversation_id'
使用:
'conversations.conversation_id'