时间戳

首页 > 解决方案 > MYSQL PHP JSON 返回每个产品的所有图像

php - MYSQL PHP JSON 返回每个产品的所有图像

问题描述

我需要这个 JSON 输出


    {
        "ID": "1",
        "ProductName": "aaaaaaa",
        "strImageName ": {
                          "image1":"image_for_1.jpg",
                          "image2":"image_for_2.jpg",
                          "image3":"image_for_3.jpg"
                         }
    },
    {
        "ID": "2",
        "ProductName": "bbbbbb",
        "strImageName ": {
                          "image1":"image_for_1.jpg",
                          "image2":"image_for_2.jpg"

                         }
    },
    {
        "ID": "3",
        "ProductName": "ccccccc",
        "strImageName ": {
                          "image1":"image_for_1.jpg"


                         }
    },

-------------------------- MYSQL ------------------------

表结构非常相似：

CREATE TABLE Products(
    ProductID int(11) NULL AUTO_INCREMENT,
    ProductName varchar(255) NULL,
    PRIMARY KEY (intProductID)
);

 CREATE TABLE Products_images (
    ImageID int(11) NULL AUTO_INCREMENT,
    ProductID int(11) NULL,
    strImageName varchar(255) NULL,
    PRIMARY KEY (intImageID)
);

用以下内容填充表：

INSERT INTO Products(ProductName)
VALUES
('Product #1'), ('Product #2'), ('Product #3');

INSERT INTO tblProductImages (ProductID, strImageName) 
VALUES
(1, 'image_for_1.jpg'),
(1, 'image_for_2.jpg'),
(1, 'image_for_3.jpg'), 
(2, 'image_for_1.jpg'), 
(2, 'image_for_2.jpg'),
(3, 'image_for_1.jpg');

我想做一个返回所有 3 种产品的 SELECT，其中包含每个产品的所有图像。如同：

ProductID  ProductName  strImageName
1          Product #1   image_for_1.jpg
2          Product #1   image_for_2.jpg
3          Product #1   image_for_3.jpg
4          Product #2   image_for_1.jpg
5          Product #2   image_for_2.jpg
6          Product #3   image_for_1.jpg

我的简单解决方案：

"SELECT 
                p.ProductID ,
                p.ProductName ,
                i.strImageName 
              FROM 
                 Products p
              LEFT JOIN tblProductImages i
                 ON p.ID = i.ProductID 
              ";

----------------------PHP----------------------

$num = $result->rowCount();
if($num > 0) {
    // Post array
    $product_arr = array();
 while($row = $result->fetch(PDO::FETCH_ASSOC)) {
      extract($row);
      //echo $Designation. "\r\n";

     $product_item= array(
        'ID' =>$ID,
        'ProductName' => $ProductName,
        'strImageName' => $strImageName
      );  

      // Push to "data"
      array_push($product_arr, $product_item);


    }
    // Turn to JSON & output
    echo json_encode($posts_arr);
  } else {
    // No Products found
    echo json_encode(
      array('message' => 'No Products Found')
    );
  }

- - - - - - - - - - - 问题 - - - - - - - - - - -

当我这样做时，输出是

{"ID": "1",
  "ProductName": "aaaaaaa",
  "strImageName ":"image_for_1.jpg"} ,
{"ID": "1",
  "ProductName": "aaaaaaa",
  "strImageName ":"image_for_2.jpg"} ,
{"ID": "1",
  "ProductName": "aaaaaaa",
  "strImageName ":"image_for_3.jpg"} ,


{"ID": "2",
  "ProductName": "aaaaaaa",
  "strImageName ":"image_for_1.jpg"} ,
{"ID": "2",
  "ProductName": "aaaaaaa",
  "strImageName ":"image_for_2.jpg"} ,


{"ID": "3",
  "ProductName": "aaaaaaa",
  "strImageName ":"image_for_1.jpg"}

谢谢

标签： phpmysqljson

解决方案

您应该以某种方式按产品 ID 对图像进行分组。因此，例如，您可以执行以下操作：使用键值数组查看产品是否已提取，如果没有，则将产品及其第一个图像保存到我们的数组中，否则：仅将图像插入到与该产品相关的图像数组。

if (isset($product_arr[$ID])) {
     //if the product is already inserted into our array, just insert another image to the extracted images of that product
     $product_arr[$ID]['strImageName'] [] = $strImageName;
} else {
     //if we see the product for the first time, insert the product to our array
     $product_arr[$ID] = array(
        'ID' => $ID,
        'ProductName' => $ProductName,
        'strImageName' => array($strImageName)
      );
}

//then
echo json_encode(array_values($product_arr));

推荐阅读