如何在Codeigniter中获取具有特定ID的最后一条记录

问题描述

这里我有2张桌子。第一个是客户，第二个是会员身份

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

现在我只想获得一条带有这些绑定表中的特定 customer_id （客户和成员身份）。

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

这是我的客户表结构

此处为我的会员资格表结构和所需的最新插入的行

这里我自己尝试过一个代码

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;

        }

    }

但返回来自两个联接表的所有行。但是我只想要每个客户id的最新（最后）行。.

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

推荐答案

要根据最高ID从会员表中获取每个客户的最新记录，可以通过调整来自动加入会员

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

普通的SQL类似于

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

这篇关于如何在Codeigniter中获取具有特定ID的最后一条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！