Mario Mario - 1 month ago 6
JSON Question

Creating a nested JSON object from SQL results

I'm trying to figure out how to create a nested JSON object, something like this:

company: "Company 1",
pricing: {
term: "1 year",
price: "$4.95",

term: "2 years",
price: "3.95"
},


I have two tables in MySQL, one called plans which is structured in this fashion

| id | company |
------------------
| 2 | company 1 |


and another table plans_pricing to represent the pricing data

| id | plans_id | term | price |
--------------------------------
| 1 | 2 | 1 year | $4.95 |
| 2 | 2 | 2 years| $3.95 |


I am using Laravel 4 to query the database and create json to send back to my ajax request. Here is the query, which is currently sending a server 500 error.

public function results()
{
$answers = $_POST['answers'];

$data = DB::table('plans')
->join('plans_pricing', 'plans.id', '=', 'plans_pricing.plans_id')
->select('plans.company', 'plans_pricing.price', 'plans_pricing.term')
->whereIn('plans.id', $answers)
->get();

echo json_encode($data);
}


I'm not sure why this query isn't working, but that isn't even why i'm asking this question. I need to know how to get a nested JSON object, when I create the join, I believe that I'll receive a separate object for each, like here:

| company | price | term |
------------------------------------
| company 1 | 4.95 | 1 year |
| company 1 | 3.95 | 2 years|


How can I make this SQL query return a nested JSON object like the one I describe above? I have been stuck on this problem for two days now and could really use some guidance. Thanks

UPDATE:

The server 500 error was fixed by changing
echo json_encode
to
return Response::json($data);

Answer

I've never used Laravel but I think this should work:

$output = array();
$currentCompany = "";

foreach ($data as $datum) {
  if ($datum->company != $currentCompany) {
    $output[] = array();

    // get a reference to the newly added array element
    end($output);
    $currentItem = & $output[key($output)];

    $currentCompany = $datum->company;
    $currentItem['company'] = $currentCompany;
    $currentItem['rates'] = array();
  }
  $currentItem['rates'][] = array("price" => $datum->price, "term" => $datum->term);
}

json_encoded result:

[{
    "company":"company 1",
    "rates":[{
      "price":4.95,"term":"1 year"
    },{
      "price":3.95,"term":"2 years"
    }]
}]
Comments