I want to delete 5 messages in Ascending order from the table if any user has sent 6 messages to other user and show the 6th message. For example if Tapy has sent 6 messages to Nomi than first 5 messages will be deleted in ascending order and 6th message will be shown. I have counted number of messages sent but can't find out the way to delete first 5 messages.
Table name :
The fields in the database are:
Here is the model
function deletemessages($data)
{
$this->db->select('message as receiver,timestamp');
$this->db->from('t_chat_msg');
$this->db->where('from_user_email', $data['fromuser']);
$this->db->where('to_user_email', $data['touser']);
$this->db->order_by("timestamp", "asc");
$num=$this->db->count_all_results();
return $num;
}
Here is the controller
public function deletemessage()
{
$data = json_decode(file_get_contents('php://input'));
$touser = $data->{'touser'};
$fromuser = $data->{'fromuser'};
$data = array('touser' => $touser,
'fromuser' =>$fromuser);
$status=$this->um->deletemessage($data);
echo json_encode($status);
}
To get how much messages in total one user have sent to another use query
SELECT COUNT(*) AS K FROM t_chat_msg WHERE touser='$touser' AND fromuser='$fromuser'
Then you have a total number of messages, you can delete old one leaving desired amount of messages using query
DELETE FROM t_chat_msg ORDER BY timestamp ASC LIMIT $limit
IF PUT EVERYTHING TO FUNCTION
// $amt - amount of messages to keep
// $fromuser - message sender
// $touser - message recipient
function delete_old($amt,$fromuser,$touser)
{
$ma=mysqli_fetch_assoc(mysqli_query($db,"SELECT COUNT(*) AS K FROM t_chat_msg WHERE touser='$touser' AND fromuser='$fromuser'"));
$limit=$ma['K']-$amt;
mysqli_query($db,"DELETE FROM t_chat_msg ORDER BY timestamp ASC LIMIT $limit");
}
For codeigniter should work something like this
// $amt - amount of messages to keep
// $fromuser - message sender
// $touser - message recipient
function delete_old($amt,$fromuser,$touser)
{
$limit=$this->db->query("SELECT COUNT(*) FROM t_chat_msg WHERE touser='$touser' AND fromuser='$fromuser'")->row()-$amt;
$this->db->query("DELETE FROM t_chat_msg ORDER BY timestamp ASC LIMIT $limit");
}
I don't use it, but based on this it should work properly.