How to return json object with json array inside of it?

Question

i have many to many relationship like this : Server version: 10.4.17-MariaDB

table colors(id,name).
table items(id,title....).
table item_color(id,items_id,color_id).

my query is like this :

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

i use php function json_encode(). how to return this :

{
    "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"
      ]

  }

instead if this:

    "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"
  },

aggregate by color and group by all other columns

Akina
– Akina

2021-01-30 17:11:56 +00:00
Commented Jan 30, 2021 at 17:11 — Akina
– Akina, Commented Jan 30, 2021 at 17:11

Hanief Han · Accepted Answer · 2021-01-30 17:32:55Z

There's 2 way to do it:

If you want to keep your query, you must parse the data first before you parse it to 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)
...

Or you could just...

make the query 2 part, first is for get all items, second is for get the colors for it

...
$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)
...

mohamed khounti · Accepted Answer · 2021-01-30 19:20:26Z

i wanted to add also category so this is what i came with

function regroup_color($data = array(),$data2 = array()){
  // array that will hold our data and we will return it later
$ret = array();
// fetch the data as d
foreach($data as $d){
  //save the id 
  $id = $d['id'];
  //make sure no id was set
  if(!isset($ret[$id])){
    // save the name of the color
    $color = $d['color'];
    unset($d['color']);
    //save all the item data
    $ret[$id] = $d;
    // add color to color array
    $ret[$id]['color'][] = $color;
  }else{
    // if wa alredy did all the above things just keep adding colors to color array
    $ret[$id]['color'][] = $d['color'];
  }
}
  
  
foreach($data2 as $d){

    //save the id 
      $id = $d['id'];
      //make sure no id was set
      if(!isset($ret[$id])){
        // save the name of the color
        $category = $d['category'];
        unset($d['category']);
        $ret[$id] = $d;
        $ret[$id]['category'][] = $category;
      }else{
        $ret[$id]['category'][] = $d['category'];
      }
    }

return $ret; }

result :

"22": {
"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": [
  "black",
  "white",
  "pink",
  "red",
  "yellow"
],
"category": [
  "buttom",
  "hats",
  "shoes"
]

}

Collectives™ on Stack Overflow

How to return json object with json array inside of it?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related