multidimensional Array php

Question

I am trying to build multidimensional to get json but its getting little complex. Sure there is easier way to do this. Here is my code.

$rows = array();
$idx = 0;
$sql = "SELECT products, GROUP_CONCAT(title,',' ,price SEPARATOR ', ' ) prods FROM mylist GROUP BY products";       
$query = mysqli_query($con, $sql); 
while($row = mysqli_fetch_assoc($query)){
    $rows[$idx]['products'] = $row['products'];
    $title = explode(',',$row['prods']);
    $rows[$idx]['prods'] = $title;
    $idx++;
};
echo '<pre>' . var_export($rows, true) . '</pre>';
echo json_encode($rows);

It give me this result

array (
  0 => 
  array (
    'prods' => 
    array (
      0 => 'title 4',
      1 => '4',
      2 => ' title 1',
      3 => '1',
    ),
  ),
  1 => 
  array (
    'prods' => 
    array (
      0 => 'title 2',
      1 => '21',
    ),
  ),
  2 => 
  array (
    'prods' => 
    array (
      0 => 'title 3',
      1 => '3',
    ),
  ),
)
[{"prods":["title 4","4"," title 1","1"]},{"prods":["title 2","21"]},{"prods":["title 3","3"]}]

But I want like this

    array (
  0 => 
  array (
    'prods' => 
    array (
      array(title => 'title 4', price => '4'),
      array(title => ' title 1', price => '1'),
    ),
  ),
  1 => 
  array (
    'prods' => 
    array (
      array(title => 'title 2', price => '21'),
    ),
  ),
  2 => 
  array (
    'prods' => 
    array (
      array(title => 'title 3', price => '3'),
    ),
  ),
)



    [
    {
        "prods": [
            {
                "title": "title 4",
                "price": "4"
            },
            {
                "title": "title1",
                "price": "1"
            }
        ]
    },
    {
        "prods": [
            {
                "title": "title2",
                "price": "21"
            }
        ]
    },
    {
        "prods": [
            {
                "title": "title3",
                "price": "3"
            }
        ]
    }
]

So, not sure how to mix the exploded data on array to get the right json encoded.

Answer 1

Based on Tristan earlier suggestion, I was able to get the expected result with below code, not sure if that's the best way.

$rows = array();
$idx = 0;
$sql = "SELECT products, GROUP_CONCAT(title,',',price SEPARATOR ';') prods FROM mylist GROUP BY products";       
$query = mysqli_query($con, $sql); 

while($row = mysqli_fetch_assoc($query)){
    $prods = explode(';',$row['prods']);
    foreach ($prods as $key => $value) {
        $expValue = explode(',',$value);
        $rows[$idx]['prods'][] = array('title' => $expValue[0], 'price' => $expValue[1]);
    };
    $idx++;
};
echo '<pre>' . var_export($rows, true) . '</pre>';

echo json_encode($rows);

Answer 2

You can do a foreach on the $title array and if the key is even then build the output array with the desired keys. In the below I edited to substituted your db query for json so that the full example can be included without the sql query.

$rows = array();
$idx = 0;
$data = json_decode('[{"products":"the product", "prods":"title 4,4,title 1,1"},{"products":"the product", "prods":"title 2,21"},{"products":"the product", "prods":"title 3,3"}]', true);
foreach ($data as $row)
{
  $rows[$idx]['products'] = $row['products'];
  $title = explode(',',$row['prods']);
  foreach ($title as $k => $v) {
    // check if even
    if ($k % 2 == 0) $rows[$idx]['prods'][] = array('title' => $v, 'price' => $title[$k+1]);
  }
  $idx++;
};
echo '<pre>' . var_export($rows, true) . '</pre>';
echo json_encode($rows);

Will output the following

array (
  0 => 
  array (
    'products' => 'the product',
    'prods' => 
    array (
      0 => array ('title' => 'title 4','price' => '4'),
      1 => array ('title' => 'title 1','price' => '1'),
    ),
  ),
  1 => 
  array (
    'products' => 'the product',
    'prods' => 
    array (
      0 => array ('title' => 'title 2','price' => '21'),
    ),
  ),
  2 => 
  array (
    'products' => 'the product',
    'prods' => 
    array (
      0 => array ('title' => 'title 3','price' => '3'),
    ),
  ),
)