php - Mysqli 从两列中获取数据

Question

我有3个表users(id,name,etc..)，trips(id,user_id,from,to,destionation)，vactions(id,user_id,from,to,destionation)。我使用 mysqli，但我不知道如何做到这一点我需要像这样获取这个表

Array(    [0] => Array        (            [id] => 1            [current_status] => 0            [username] => user1            [fullname] => Eric Norman            [trips] = > Array            (                [0] => Array                 (                    [date_from] = 02/06/14                    [date_to] = 05/06/14                    [destination] = "Grece"             )           [vacations] = >         )    [1] => Array        (            [id] => 2            [current_status] => 0            [username] => user2            [fullname] => Joe Grey            [trips] = > Array            (                [0] => Array                 (                    [date_from] = 02/06/14                    [date_to] = 05/06/14                    [destination] = "Grece"             )            [vacations] = >          ))

我尝试过左连接，但不起作用，我的代码现在仅适用于用户:

    conn = new mysqli($host,$user,$password,$db);    $result = mysqli_query($conn, "SELECT id, current_status, username, CONCAT(first_name,'  ',last_name) as fullname FROM user");    while ($row = $result->fetch_assoc()) {            $users[] = $row;        }

Answer 1

我认为最好的方法是获取所有用户，然后使用 foreach 获取所有旅行和假期。因为我不确定你是否可以使用简单的 SQL 查询来获取这种数组结果，所以我认为你需要一个 ORM。

$users = getAllUsers();
foreach($users as $user){
  $user['trips'] = getTripsByUserId($user['id']);
  $user['vacations'] = getVacationsByUserId($user['id']);
}

当然你需要编写 3 个方法“getAllUsers”； “getTripsByUserId”和“getVacationsByUserId”只是使用 where 子句对数据库进行简单的 SELECT 查询。