这里我有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的最后一条记录
发布评论