使用 PDO 从拖曳表中获取 JSON

Question

我正在尝试从MySQL数据库中的两个表中获取JSON对象，但没有返回任何内容。

product table: id, title, description, price
product_colors table: id, product_id, product_color

我的PHP代码：

$st = $conn->prepare ('SELECT `product`.id , `product`.title, `product`.description, `product`.price, GROUP_CONCAT(`product_colors`.product_color) AS colors FROM `product` LEFT JOIN `product_colors` ON `product`.id = `product_colors`.product_id GROUP BY `product`.id');

$st->execute();
$products = [];
$rows = $st->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as $row) {
$row['product_colors'] = explode(',', $row['product_color']);
$products[] = $row;
}
echo json_encode ($products);

这是我想要得到的：

[
{
id: 4,
title: 'Car',
description: "Pellentesque orci lectus",
price: '120$',
product_color: ['Red', 'Blue', 'Black']
},
{
id: 6,
title: 'Bus',
description: "orci lectus",
price: '10$',
product_color: ['White', 'Blue', 'Green']
}
]

Answer 1

所以最后我得到了答案，首先查询是正确和完美的，获取和循环中的错误，所以这是完美的解决方案，而且容易得多：

$rows = array();
while($r = mysqli_fetch_assoc($sth)) {
$r['colors']=explode(',', $r['colors']); //colors like what i named the GROUP_CONCAT
$rows[] = $r;

Answer 2

还在考虑PDO吗？你可能想要这个...

<?php
$output = array();
$products = 'product'; // "product" Table
$colors = 'product_colors'; // "product_colors" Table
//if "id" is auto-incremented which may not match "product_id", You may want to create another "product_id" in $products table to use the $statement commented below
/*
$statement = $connection->prepare(
"SELECT t.product_id, t.title, t.description, t.price, c.product_color FROM `$products` t, `$colors` c WHERE t.product_id = c.product_id"
);
*/
$statement = $connection->prepare(
"SELECT t.id as product_id, t.title, t.description, t.price, c.product_color FROM `$products` t, `$colors` c WHERE t.product_id = c.product_id"
);
$statement->execute();
$result = $statement->fetchAll();
foreach($result as $row)
{
$output['id'] = $row['product_id'];
$output['title'] = $row['title'];
$output['description'] = $row['description'];
$output['price'] = $row['price'];
$output['product_color'] = explode(',', $row['product_color']);
}
echo json_encode($output);
?>