php - 从一个表中选择匹配值到另一个表中的多个值

Question

    cars                 models
car_id  car       car_id   model_id  model
   1    ford         1         1      mustang
   2    fiat         1         2      focus
   3    toyota       1         3      escort
                     2         4      500
                     2         5      spider
                     3         6      tacoma

我的两个表要复杂得多，所以我从另一个用户的问题中获取了这段代码，这几乎是我想要的，但我不知道如何使用 PHP 正确获取输出格式

SELECT c.Car, m.Model_id, m.Model
FROM models m
INNER JOIN car c ON c.Car_id = m.Car_id
WHERE m.Car_id = (SELECT Car_id FROM models WHERE Model = 'Escort');

$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
while($row = mysqli_fetch_assoc($result)) {

$model = $row['Model'];
$vehicle = $row['Car'];

    ford mustang
    ford focus
    ford escort

我想要得到的是

    ford
        mustang
        focus
        escort

我在下面发布了我的更新尝试，它有效，但我敢打赌，优秀的人可以让它变得更漂亮。

$sql = "
SELECT c.Car, m.Model_id, m.Model 
FROM models m 
INNER JOIN car c   
ON     c.Car_id = m.Car_id
WHERE m.Car_id = '1' ";

$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
while($row = mysqli_fetch_array($result)) {
$vehicle[$row['Car']][] = $row['Model'];
}

echo "<table>";
foreach( $vehicle as $value => $key )
{echo '<tr><td>'.$value.'</td></tr>'; 
foreach( $key as $mod) 
{echo '<tr><td>'.$mod.'</td></tr>';}}

}

结果:
福特
野马
焦点
护送

Answer 1

您可能需要一个以 key 作为汽车类型的数组。循环结果并根据需要附加到数组。

例如:

vehicle=[];
while($row = mysqli_fetch_assoc($result)) {
    if (!array_key_exists($row['Car'], $vehicle)){
        $vehicle[$row['Car']] = [];
    }
    $vehicle[$row['Car']][] = $row['Model'];
}

输出将如下所示:

array(1) {
  ["Ford"]=>
  array(2) {
    [0]=>
    string(4) "focus",
    [1]=>
    string(6) "escort"
  }
}