I am trying to build a messaging system in CodeIgniter. I have three tables as below.
messages:
messages_thread:
messages_participants:
I am easily able to compose a new message, and the recipient can see that a message has been received.
The problem comes when there is a reply. After replying the results are duplicated. It show that the initial message was sent from both the originator and the receiver and is returning 4 rows instead of the expected 2 rows. Can anyone tell me where I am going wrong?
Here is my model:
function reply_to_thread(){
//Generate random string for the ticket number
$rand = substr(str_shuffle(MD5(microtime())), 0, 11);
//Add the random string to the hash
$messageid = $rand;
$messageinsert = array(
'message_id' => $messageid,
'subject' => $this->input->post('subject'),
'message' => $this->input->post('message'),
'thread_id' => $this->input->post('thread_id'),
);
$participantsinsert = array(
'message_id' => $messageid,
'from_id' => $this->input->post('from_id'),
'to_id' => $this->input->post('to_id'),
'thread_id' => $this->input->post('thread_id'),
);
$this->db->insert('messages',$messageinsert);
$this->db->insert('messages_participants',$participantsinsert);
}
function view_thread($thread_id){
$this->db->where('messages.thread_id', $thread_id);
$this->db->join('messages_participants', 'messages_participants.thread_id = messages.thread_id');
$this->db->join('users', 'messages_participants.from_id = users.id');
$result = $this->db->get('messages');
var_dump($result);
return $result->result_array();
}
My controller:
function check_messages(){
$id = $this->session->userdata('id');
$data['thread'] = $this->message_model->check_messages($id);
$this->load->view('messages/my_messages', $data);
}
function view_thread($thread_id){
$data['thread'] = $this->message_model->view_thread($thread_id);
$this->load->view('messages/view_thread',$data);
}
function reply(){
$this->message_model->reply_to_thread();
redirect('messages/get_messages');
}
My view thread view:
<div class="well">
<h3>View Messages</h3>
<?php foreach($thread as $threadfield):?>
<div class="message">
<img class="avatar pull-left" src="<?php echo $threadfield['avatar'];?>">
<div class="message-actions">
<button class="btn btn-success" id="reply">Reply</button>
</div>
<p>
<strong>
From: <a href="profile.html"> Users id: <?php echo $threadfield['from_id'];?></a>
</strong>
<span class="badge badge-important">Unread</span><br>
<strong>Date:</strong>
<?php echo date('M j Y g:i A', strtotime($threadfield['sent']));?>
</p>
<p><strong>Subject:</strong>
<a href = "/messages/view_thread/<?php echo $threadfield['thread_id'];?>">
<?php echo $threadfield['subject'];?>
</a>
</p>
<p><strong>Message:</strong> <?php echo $threadfield['message'];?></p>
<hr>
</div>
<?php endforeach; ?>
</div>
<div class="row-fluid" id="replyform" style="display: none">
<div class="well">
<h3>Reply to <?php echo $threadfield['username'];?>'s Message.</h3>
<?php echo form_open('messages/reply');?>
<input type="text" value="<?php echo $threadfield['thread_id'];?>" name="thread_id" id="thread_id">
<input type="text" value="<?php echo $this->session->userdata('id');?>" name="from_id" id="from_id">
<input type="text" value="<?php echo $threadfield['from_id'];?>" name="to_id" id="to_id">
<input type="text" value="RE: <?php echo $threadfield['subject'];?>" name="subject" id="subject">
<input type="text" placeholder="Message......." name="message" id="message">
<button class="btn" type="submit">Submit Reply</button>
<?php echo form_close();?>
</div>
EDIT:
I managed to solve this problem but it created a new one. I solved it by modifying the joins so the messages we not tied to the users but the participants were, now though, i have no way of showing the correct userdata to display the initial new message, it will always show either the last senders details or the last recipients details. This is depending on if i set the join to from_id or to_id. Does anyone know how i can tie in the user table join to the last message?
I don't see any ORDER BY in your query to show the latest record.
function view_thread($thread_id){
$this->db->where('messages.thread_id', $thread_id);
$this->db->join('messages_participants', 'messages_participants.thread_id = messages.thread_id');
$this->db->join('users', 'messages_participants.from_id = users.id');
$this->db->order_by('field_name_goes_here');//put the name of field to set order by..
$result = $this->db->get('messages');
return $result->result_array();
}