codeigniter

Delete 5 messages if two users have sent 6 messages to each other


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 :

  1. t_chat_msg

The fields in the database are:

  1. chat_msg_id
  2. message
  3. from_user_email
  4. to_user_email
  5. timestamp

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);
}

Solution

  • 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.