什么查询可以让我得到这个结果？JSON和PHP

debugcn 发表于 Dev

LACROIX

{
    "cars": {
        "toyota": [
            {"model":"*****", "doors":"*","color":"*****"},
            {"model":"*****", "doors":"*","color":"*****"}
                  ],
        "Ford": [
            {"model":"*****", "doors":"*","color":"*****"},
            {"model":"*****", "doors":"*","color":"*****"}
        ]
    }
}

什么查询会给我以下结果？

这是一个JSON对象（汽车），其中包含用于不同汽车模型的多个数组。在每个数组中，将包含其他类型的数据，例如门，颜色，年份...等等。

我尝试了这段代码：

<?php 

require 'conn_pdo.php';

$conn -> setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC); 

$sql = "select * from test_cars where cars in (select cars from test_cars GROUP BY cars) ORDER BY RAND()";

$stmt = $conn -> prepare($sql);
$stmt -> execute();
$row = $stmt -> fetchAll();
$json['cars'] = $row;
echo json_encode($json);

?>

但是结果不是我希望的

{ "cars": 
         [ {"model":"*****", "doors":*","color":*****"},  
           {"model":"*****","doors":"*","color":*****"} ,
           {"model":"*****", "doors":"*","color":*****"}, 
           {"model":"*****", "doors":"*","color":*****"},
           .......
         ] 
}

我得到了汽车对象，其中包含一个用于所有不同型号汽车的数组！

半身人

这是一种方法。我删除了该GROUP BY方法，因为问题中的方法未正确使用它-它通过使用IN查询将分组扔掉了。我认为用PHP还是很容易做到的。

// It would be good to check to ensure you have a valid connection,
// and that the prepare/execute calls are successful
$sql = "SELECT * from test_cars";
$stmt = $conn->prepare($sql);
$stmt->execute();
$rows = $stmt->fetchAll();

$out = [];
foreach ($rows as $row)
{
    // Let's process by brand (since you have not shown your
    // schema, you will need to adjust column names to suit)
    $brand = $row['brand'];

    // Get other data
    $model = $row['model'];
    $doors = $row['doors'];
    $colour = $row['colour'];

    // Check to ensure the brand is an array, so that we don't
    // get an error when we push
    if (!isset($out[$brand]))
    {
        $out[$brand] = [];
    }

    // Now push the data on to the end (using [])
    $out[$brand][] = [
        'model' => $model,
        'doors' => $doors,
        'colour' => $colour,
    ];
}

$json['cars'] = $out;
echo json_encode($json);