{
"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);
这是未经测试的，但应该给你一个大致的想法。您应该期望必须对其进行一些调整，就像Stack Overflow上的所有答案一样。我使用了新的数组语法(从 PHP 5.4 开始(，但如果您将其换成 array()，它将适用于早期版本。