php - 国家、州和城市名称显示第一个表中的正确名称，但也显示第二个表中的相同详细信息

Question

我有 tbl_customer、航运、国家、州和城市表和列

tbl_customer

cust_id | Name  | Email         |b_address  | b_country | b_State | b_city 
1       | zxxzc | zxz@gmail.com |asdasasdsa | 231       | 3936    | 45645
2       | ergtf | okh@gmail.com |hghggjhghg | 231       | 3948    | 45497
3       | oiuyt | ert@gmail.com |mkjhgfdddd | 231       | 3927    | 43472

运费

s_id | s_address  | s_country | s_State | s_city | cust_id
1    | asdasasdsa | 231       | 3934    | 44173  | 1
2    | oiuytrjhhg | 13        | 273     | 6815   | 3

现在我必须从两个表中获取国家、州和城市名称。所以我尝试加入像

$this->db->select("*")
$this->db->from('tbl_customer');
$this->db->join('shipping', 'tbl_customer.cust_id=shipping.cust_id', 'LEFT');
$this->db->join('countries', 'countries.id=tbl_customer.b_country OR countries.id=shipping.s_country'); 
$this->db->join('states', 'states.id=tbl_customer.b_State OR states.id=shipping.s_State'); 
$this->db->join('city', 'city.id=tbl_customer.b_city OR city.id=shipping.s_city'); 

 $query = $this->db->get();
 $result = $query->result();
if($result)
      {
        return $result;
      }
       else 
      {
        return 0;
       }

Controller

$list_1=$this->Reports_model->get_details(); 
foreach($list_1 as $row)
  {
/*customer table*/
$countryname=$row->country_name;
$state_name=$row->state_name;
$cities_name=$row->cities_name;

/*shipping table*/
$countryname_s=$row->country_name;
$state_name_s=$row->state_name;
$cities_name_s=$row->cities_name;

}

但问题是，我得到了相同的国家名称、州名称和城市名称。我的意思是 tbl_customer 详细信息显示正确，但运输详细信息也显示相同的详细信息。

我想我必须使用别名来显示运送详细信息。

Answer 1

运输和客户表列名称不同。

 foreach($list_1 as $row)
  {
/*customer table*/
$countryname=$row->b_country;
$state_name=$row->b_state;
$cities_name=$row->b_city;

/*shipping table*/
$countryname_s=$row->s_country;
$state_name_s=$row->s_state;
$cities_name_s=$row->s_city;

}

要显示国家/地区、州和城市名称，您选择的查询应如下所示。

$this->db->select("c.*, c1.name as country_name, c2.name as shipping_country, s1.name as state_name, s2.name as shipping_state, ci1.name as city_name, ci2.name as shipping_city")
$this->db->from('tbl_customer c');
$this->db->join('shipping s', 'c.cust_id=s.cust_id', 'LEFT');
$this->db->join('countries c1', 'c1.id=c.b_country'); 
$this->db->join('countries c2', 'c2.id=s.s_country'); 
$this->db->join('states s1', 's1.id=c.b_State'); 
$this->db->join('states s2', 's2.id=s.s_State'); 
$this->db->join('city ci1', 'ci1.id=c.b_city'); 
$this->db->join('city ci2', 'ci2.id=s.s_city');