php - 如何返回其中包含 json 数组的 json 对象？

Question

我有多对多的关系像这样 :服务器版本:10.4.17-MariaDB

• 表格颜色(id，名称)。
• 表项(id，标题....)。
• 表 item_color(id,items_id,color_id)。

我的查询是这样的:

SELECT items.*,colors.name FROM items,item_color,colors
 where
 items.id = item_color.item_id
 and
 colors.id = item_color.color_id

我使用 php 函数 json_encode()。如何返回:

{
    "id": "22",
    "title": "my products 515151",
    "descreption": "5454545455",
    "price": "0.05",
    "quantity": "2",
    "date_added": "2021-01-29 14:37:24",
    "primary_image": "http://localhost/ecomerce/uploads/1611927444hat.jpg",
    "color": [
      0 : "pink",
      1 : "white"
      ]

  }

相反，如果这样:

    "id": "22",
    "title": "my products 515151",
    "descreption": "5454545455",
    "price": "0.05",
    "quantity": "2",
    "date_added": "2021-01-29 14:37:24",
    "primary_image": "http://localhost/ecomerce/uploads/1611927444hat.jpg",
    "color": "pink"
  },
  {
    "id": "22",
    "title": "my products 515151",
    "descreption": "5454545455",
    "price": "0.05",
    "quantity": "2",
    "date_added": "2021-01-29 14:37:24",
    "primary_image": "http://localhost/ecomerce/uploads/1611927444hat.jpg",
    "color": "red"
  }, 

Answer 1

有两种方法:

1. 如果要保留查询，必须先解析数据，然后再将其解析为 json。

...
function regroup_color($data = array()){
  $ret = array();
  foreach($data as $d){
    $id = $d['id'];
    if(!isset($ret[$id])){
      $color = $d['color'];
      unset($d['color']);
      $ret[$id] = $d;
      $ret[$id]['color'][] = $color;
    }else{
      $ret[$id]['color'][] = $d['color'];
    }
  }
  return $ret;
}
$data = regroup_color($data);
echo json_encode($data)
...

或者你可以...

1. 将查询分为 2 部分，首先是获取所有 items，其次是获取它的 colors

...
$query = "SELECT * FROM items";
// get the data here using query above
$data = {{result of query}};

foreach($data as $i => $d){
  $id = $d['id'];
  $query = "SELECT * FROM item_color JOIN colors ON item_color.color_id = colors.id";
  // get the data here using query above
  $colors = {{result of query}};
  foreach($colors as color){
    $data[$i]['color'][] = $color['name'];
  }
}
echo json_encode($data)
...