Creating a nested JSON object from SQL results

Question

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);

typically an array of ids, such as 2, 3 , 4. But for this simplicity just assume $answers = 2, the id of company1 — Mario
– Mario, Commented Apr 3, 2014 at 17:03
have you verified that your query is executing properly, like check to see if it errors out, or that $data actually contains rows — Patrick Evans
– Patrick Evans, Commented Apr 3, 2014 at 17:03
I don't receive any errors, aside from a server 500 error when I console.log the ajax request. The query was working great until I added the join — Mario
– Mario, Commented Apr 3, 2014 at 17:06
If you are using laravel instead of echo json_encode use return Response::json($data); Also if you are in chrome open you developer tools go to network and select the ajax request when you make it, what error does it give? — Rick Lancee
– Rick Lancee, Commented Apr 3, 2014 at 17:07

James · Accepted Answer · 2014-04-03 17:59:29Z

3

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

answered Apr 3, 2014 at 17:59

James

22.4k5 gold badges29 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Mario Over a year ago

I thought that I considered myself decently fluent in PHP, but I'm confused about a few parts of your solution. Why do you declare the $output[] = array(); inside of the foreach loop as well as outside? Why do you use end($output) and the line i'm most confused about is $currentItem = & $output[key($output)]; what is this line doing? Thanks again for your answer, it works beautifully, i'm just confused about how it works

James Over a year ago

I declare the entire variable $output to be an array (line 1). Inside the loop, if the company is a NEW company, I create a new element in $output and set that to be an empty array as well. Then I create a reference to the new array element - that's what the two lines prefixed with "get a reference to the newly added array element" do. There are other ways to achieve the same result without using a reference.

Mario Over a year ago

interesting, thanks for the explanation. I really appreciate it

Jeaf Gilbert Over a year ago

@James Does this conditional statement: if ($datum->company != $currentCompany) mean the same $datum->company value won't appear again after the other values?

James Over a year ago

@JeaffreyGilbert Correct. The idea was to create one array element per company, with other $data values added to the existing element (for any given company). For this to work the input list ($data) has to be sorted by company so all records for the same company are together.

Collectives™ on Stack Overflow

Creating a nested JSON object from SQL results

1 Answer 1

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Related