How to get country,state,city name in the most optimized way from different tables

0
votes

I have a employee table,city country and state table. My employee table stores id of country state and city. I want country_name, state_name, city_name using mysqli query in optimized form.

employee table

emp_id  name  email         country  state   city
------- ----   -----         -------  ------  -----
    1    abc  [email protected]     1      1       1

country table

country_id    country_name
-----------   -------------
1                India

state table

state_id  country_id  state_name
--------  ----------  -----------
1            1          Gujarat

city table

city_id   state_id  city_name
-------    -------   --------
1            1       Ahmedabad

Function used to fetch the data

  function select_employee(){
      $sth =  $this->con->prepare("SELECT  * from employees");
      $sth->execute();
      $result = $sth->fetchAll(PDO::FETCH_ASSOC);

      return $result;
  }

Function to display state,city,country

  function select_places(){
          $sth =  $this->con->prepare("SELECT country.country_name,state.state_name,city.city_name
                                                                            FROM employees
                                                                            LEFT JOIN country ON employees.country = country.country_id
                                                                            LEFT JOIN state ON employees.state = state.state_id
                                                                            LEFT JOIN city ON employees.city = city.city_id");
          $sth->execute();
          $result = $sth->fetchAll(PDO::FETCH_ASSOC);
          return $result;
      }

printing data

<?php
   foreach ($result as $values){
                ?>
        </tr>
        <td><?php echo $values['country']; ?></td>
        <td><?php echo $values['state']; ?></td>
        <td><?php echo $values['city']; ?></td>
     }

In the print data section i need data India,Gujarat,Ahmedabad. Not their ids.

output

country_name  state_name  city_name
------------  --------   ----------
1                1           1

what i need

 emp_id   name    email       country_name   state_name    city_name
   -----   ----   -------      ------------   --------     ----------
    1      abc    [email protected]      India         Gujarat      Ahmedabad

how can i prepare the query or join for that? that using id from employee i get name from country,state,city table

2
phpdatabasemysqlipdo
What is wrong with using JOIN? – B001ᛦ
it gives id of city state country, i need their names – Dhruv Thakkar
Because you are printing the wrong values. The value "state" will always be the id. You should use "<?php echo $values['xxx_name']; ?> instead. – Ermenegildo
editted pls check now – Dhruv Thakkar
Possible duplicate of SQL SELECT from multiple tables – Kaddath

2 Answers

3
votes

Function

 function select_employee(){
      $select = "SELECT * from employees";
      $select .= " join country on employees.country_id = country.country_id";
      $select .= " join state on employees.state_id = state.state_id";
      $select .= " join city on employees.city_id = city.city_id";
      $sth =  $this->con->prepare($select);
      $sth->execute();
      $result = $sth->fetchAll(PDO::FETCH_ASSOC);

      return $result;
  }

Printing

<?php foreach ($result as $values): ?>
    <tr>
        <td><?php echo $values['country_name']; ?></td>
        <td><?php echo $values['state_name']; ?></td>
        <td><?php echo $values['city_name']; ?></td>
    </tr>
<?php endforeach;?>
1
votes

Change $sth = $this->con->prepare("SELECT * from employees"); to

$sth =  $this->con->prepare("      
SELECT employees.* country_table.country_name, state_table.state_name, city_table.city_name FROM 
   (((employees INNER JOIN country_table ON employees.country_id = country_table.country_id)
   INNER JOIN state_table ON employees.state_id = state_table.state_id)
   INNER JOIN city_table ON employees.city_id = city_table.city_id)");