How to get single last record with a particular id in codeigniter

user_8318370 0
Viewed 1121

Here i have 2 tables. First one is customer and second on is membership

Now i just want to get a single last record with a particular customer_id from these jioned table(customer and membership).

Here is my customer table structure

enter image description here

Here is my membership table structure and desired latest inserted rows

enter image description here

Here i've tried a code myself 

    $results="";
        $this->db->select('customer.*,membership.*');
        $this->db->from('customer');
        $this->db->join('membership', 'customer.id = membership.customer_id', 'left'); 
       /* $this->db->order_by('membership.id','DESC');
        $this->db->limit('1');*/
        $query = $this->db->get();
        $data = $query->result_array();


$todayDate =  date("d-m-Y"); 
foreach ($data as $value) { 

    $this->db->select('customer.*,membership.*');
        $this->db->from('customer');
        $this->db->join('membership', 'customer.id = membership.customer_id', 'left'); 
        $this->db->where('membership.customer_id', $value['customer_id']);
        $this->db->order_by('membership.customer_id','DESC');

        $this->db->limit('1');
        $query = $this->db->get();
        $dataa = $query->result();

     foreach ($dataa as $values) {

            $date1 = new DateTime($todayDate);
            $date2 = new DateTime($values->end_date);

            $diff=date_diff($date1,$date2);
         $days =  $diff->format("%a"); 
         $pos =  $diff->format("%R");

        if($pos == "+" && $days >= 0){

            $item[] = $values;

        }         

    }

But it return all rows from both joined table. but i want only latest(last) rows with each customer id..!

Please tell me where i am going wrong. Thanks

greatest-n-per-group codeigniter mysql php
2 Answers

To get last record for each customer from membership table based on highest id you can do a self join to membership by tweaking the joining part like 

$this->db->select('c.*,m.*');
$this->db->from('customer as c');
$this->db->join('membership as m', 'c.id = m.customer_id', 'left'); 
$this->db->join('membership as m1', 'm.customer_id = m1.customer_id AND m.id < m1.id', 'left'); 
$this->db->where('m1.id IS NULL', null, false)
$query = $this->db->get();

A plain SQL would be something like

SELECT c.*,m.*
FROM customer AS c 
LEFT JOIN membership AS m ON c.id = m.customer_id 
LEFT JOIN membership AS m1 ON m.customer_id = m1.customer_id 
               AND m.id < m1.id
WHERE m1.id IS NULL
user_8533600

Change this :
$dataa = $query->result();
to this :
$dataa = $query->row();

user_85665490
Related