将JOIN SQL查询序列化为JSON的最佳方法

Question

I can't find a good title for my question. I'm doing a Restful API /JSON

My problem is:

I have got two tables:

Product: id, name
Prices: id_product, price

Product 1--* Prices (One product have got multiples prices)

So, If I want to export the next information in JSON

products: [
     {
          id:1
          name: chair
          prices: [3.3,5.0,6.0]
     }
      , {
          id:2
          name: apple
          prices: [2.0,5.0]
     } ,..
]

I do the next query:

SELECT product.id, product.name, prices.price FROM procut LEFT JOIN prices ON product.id = prices.id_product

I got the next result:

id  , name  , prices
----------------------
 1    chair    3.3
 1    chair    5.0
 1    chair    6.0
 2    apple    ...
...

I use PHP, codeigniter. Is there a way to transform in a well form JSON? Because I have to rebuild the result, doing:

        $resultArray = $query->result_array();  //CodeIgniter

        foreach($resultArray as $row)
        {
            if(!isset($resultData[$row['id']]))
            {
                $resultData[$row['id']] = $row;
                unset($resultData[$row['id']]['price']);

                $resultData[$row['id']]['prices'] = array();

            }

            $resultData[$row['id']]['prices'][] = $row['price'];
        }

to create the JSON well formed

Answer 1

That's a normal practice to sort result from SQL with PHP, so You're on the right way.

    $result = array();
    foreach($query->result_array() as $row)
    {
        $result[$row['id']] = array(
            'id'=>$row['id'],
            'name'=>$row['name'],
            'price'=>isset($result[$row['id']]['price']) ? array_push($result[$row['id']]['price'],$row['price']) : array($row['price'])
        );
    }

    $this->output
                ->set_content_type('application/json')
                ->set_output(json_encode(array('products'=>array_values($result))));