Styjun

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

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.

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