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

Question

I have tbl_customer, shipping, countries, state and city tables and columns are

tbl_customer

cust_id | Name  | Email         |b_address  | b_country | b_State | b_city 
1       | zxxzc | [e-mail] |asdasasdsa | 231       | 3936    | 45645
2       | ergtf | [e-mail] |hghggjhghg | 231       | 3948    | 45497
3       | oiuyt | [e-mail] |mkjhgfdddd | 231       | 3927    | 43472

shipping

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

Now I have to fetch country, state and city name from both the tables. So I tried joins like

$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;

}

but the issue is, I am getting the same country name, state name, and city name. I mean tbl_customer details are displaying correct but shipping details are also displaying the same details.

I think I have to use an alias name to display the shipping details.

Answer 1

Shipping and customer tables column names are different.

 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;

}

To show country, state and city name your select query should be something like below.

$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');