如何从不同的表格中以最优化的方式获取国家、州、城市名称

Question

我有一个员工表，城市国家和州表。我的员工表存储了国家和城市的id。我想country_name，state_name，city_name使用mysqli查询优化的形式。

员工表

emp_id  name  email         country  state   city
------- ----   -----         -------  ------  -----
    1    abc  [e-mail]     1      1       1

国家表

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

状态表

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

城市表

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

用于获取数据的函数

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

      return $result;
  }

显示州、市、国家的功能

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

打印数据

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

在打印数据部分，我需要数据印度，古吉拉特邦，艾哈迈达巴德。不是他们的身份证。

输出

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

我需要什么

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

我如何准备查询或加入？用雇员的ID从国家州城市表中得到名字

Answer 1

更改$sth=$this-

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

Answer 2

作用

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

印刷

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