Get the last row of the same product id for every unique payment id and update the status on a different table with codeigniter

Question

I have two tables, the first table is called payments see below

I want to to get the latest payment_id(meaning latest payment) of every product_id, see highlighted rows, in this case, I want to get product_id of 10, 9, 8, 3, and 6 but from the latest payment_id which is 69, 68, 59, 58, and 55.

After I got all of this I want to update the status on a different table called list_data, see right :

This table also has product_id the same product id from payments table.

My goal overall is to update the status of every product_id if payments have been made.
Below is my controller:

$data["exp_date"] = $this->Superadmin_model_CMS->get_all_detail_needed_again();

        $this->load->helper('date');

        foreach ($data["exp_date"] as $value) {

            $exp_date = $value->payment_endDate;
            $todays_date = date("Y-m-d");
            $today = strtotime($todays_date);
            $expiration_date = strtotime($exp_date);

            if ($expiration_date <= $today && $value->ad_status === 'active') {
                // if ($value->ad_status === 'active') {
                $update_status = 'inactive';
                $data = array('ad_status' => $update_status
                );
                $this->Listing_model->update_ads_status($value->list_data_id, $data);
                // }
            }
        }

Below is my model

public function get_all_detail_needed_again() {
    $this->db->select('py.payment_id,py.user_id,py.product_id,py.product_name,py.txn_id,py.payment_gross,py.payer_email,py.payment_status,py.payment_date,py.payment_endDate,ld.ad_status,ld.list_data_id');
    $this->db->from('payments py');
    $this->db->join('list_data ld','py.product_id = ld.list_data_id');

    $this->db->order_by('py.payment_id','desc');
    $this->db->limit(1); //If I don't put limit the code doesn't get the latest payment_id but if write limit 1, it only get 1 row of product_id and payment_id
    $query = $this->db->get();

    if($query->num_rows() > 0){
       return $query->result();
    } else{
       return array();
    }

}

public function update_ads_status($id, $data) {

    $this->db->where('list_data_id', $id);
    $this->db->update('list_data', $data);

    return true;

}

In my model, If I don't write limit 1, the code doesn't get the latest payment_id but if write limit 1 it only gets 1 row of product_id and payment_id

Can anyone help me? Thanks

Answer 1

You could try to use a subquery to select the last payment_id of each product_id first, try to modify the model like this :

public function get_all_detail_needed_again() {
    $this->db->select('py.payment_id,py.user_id,py.product_id,py.product_name,py.txn_id,py.payment_gross,py.payer_email,py.payment_status,py.payment_date,py.payment_endDate,ld.ad_status,ld.list_data_id');
    $this->db->from('payments py');
    $this->db->where('`payment_id` IN (SELECT MAX(`payment_id`) FROM `payments` GROUP BY `product_id`)');
    $this->db->join('list_data ld', 'py.product_id = ld.list_data_id');
    $query = $this->db->get();

    if($query->num_rows() > 0){
       return $query->result();
    } else{
       return array();
    }

}