我有 2 個表“products”和“products_images”，我想在同一個查詢中加載每個產品的影像，然后 json_encode 然后將其發送到前端。
products table
---- ---------------- -------
| id | product_name | price |
---- ---------------- -------
| 1 | product name 1 | 15 |
---- ---------------- -------
| 2 | product name 2 | 23.25 |
---- ---------------- -------
| 3 | product name 3 | 50 |
---- ---------------- -------
product_images table
---- ------------ -----------------------------------------------------
| id | product_id | image |
---- ------------ -----------------------------------------------------
| 1 | 1 | e5j7eof75y6ey6ke97et5g9thec7e5fnhv54eg9t6gh65bf.png |
---- ------------ -----------------------------------------------------
| 2 | 1 | sefuywe75wjmce5y98nvb7v939ty89e5h45mg5879dghkjh.png |
---- ------------ -----------------------------------------------------
| 3 | 1 | 7u5f9e6jumw75f69w6jc89fwmykdy0tw78if6575m7489tf.png |
---- ------------ -----------------------------------------------------
我希望查詢回傳這個
{
id: 5,
product_name: 'product name 1',
price: 25.23,
images: [
{
id: 1,
image: 'e5j7eof75y6ey6ke97et5g9thec7e5fnhv54eg9t6gh65bf.png'
},
{
id: 2,
image: 'sefuywe75wjmce5y98nvb7v939ty89e5h45mg5879dghkjh.png'
},
]
}
我所做的是在 php 中對每個產品進行回圈，然后加載它的影像，但我認為這不是最佳解決方案或最佳性能。
uj5u.com熱心網友回復：
/**
* Results from a SELECT * FROM products query
*/
$products = [
[
'id' => 1,
'product_name' => 'product name 1',
'price' => 15,
],
[
'id' => 2,
'product_name' => 'product name 2',
'price' => 23.25,
],
[
'id' => 3,
'product_name' => 'product name 3',
'price' => 50,
],
];
/**
* Results from either
* SELECT * FROM images
* or
* SELECT * FROM images WHERE product_id IN (?,?,?, ...) <- product ids
*/
$images = [
[
'id' => 1,
'product_id' => 1,
'e5j7eof75y6ey6ke97et5g9thec7e5fnhv54eg9t6gh65bf.png'
],
[
'id' => 2,
'product_id' => 1,
'sefuywe75wjmce5y98nvb7v939ty89e5h45mg5879dghkjh.png'
],
[
'id' => 3,
'product_id' => 1,
'7u5f9e6jumw75f69w6jc89fwmykdy0tw78if6575m7489tf.png'
],
];
foreach ($products as $i => $product) {
$products[$i]['images'] = array_filter($images, function ($image) use ($product) {
return $image['product_id'] === $product['id'];
});
}
var_export($products);
uj5u.com熱心網友回復：
SELECT p.product_id, i.image_png, ...
FROM products AS p
JOIN images AS i ON i.product_id = p.product_id
WHERE ...
ORDER BY ...
然后，在一個回圈中
echo "<img src='...$image_png ...'>";
如果您希望影像可點擊，請添加“錨點”。如果需要，添加描述性資訊。
我認為不需要 json。
如果您正在“過濾”，則將其構建到SELECT陳述句中通常會更有效。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/383198.html
上一篇：MySQL-僅在非空時連接
下一篇：按字串ID洗掉MongoDB檔案