php - 如何从与另一个表具有一对多关系的表中选择特定列？

Question

我有 2 个具有一对多关系的表(用户和位置)，每个用户有一个位置，但位置可以有许多用户。

在 users 表中，有一列名为 location_id 的列，该列与 locations 中名为 id 的列相关表。

locations.id -> users.location_id。

因此，当我想获取所有用户及其位置时，我使用以下代码:

//Select all the users.
$stmt = $pdo->prepare('SELECT * 
                        FROM users 
                            JOIN locations ON users.location_id = locations.id ');
$stmt->execute();
$values = $stmt->fetchAll(); 

//Loop through the users.
foreach($values as $val){
    $userName = $val['name'];

    //Get The location_id from users table.
    $locationId = $val['location_id']; 

    //Select the location based on this location_id.  
    $st = $pdo->prepare('SELECT * from locations WHERE id = :zid');
    $st->execute(array('zid' => $locationId));
    $v = $st->fetch();
    $location =  $v['location'];
}

是否有更好的方法来获得相同的结果？

Answer 1

据我所知，这会做同样的事情

//Select all the users.
$stmt = $pdo->prepare('SELECT * 
                        FROM users 
                            JOIN locations ON users.location_id = locations.id ');
$stmt->execute();
$values = $stmt->fetchAll(); 

//Loop through the users.
foreach($values as $val){
    $userName = $val['name'];

    //Get The location
    $location =  $val['location'];
}