Creating a multi-dimensional json based on mysql query

Question

Earlier, I used to encode the result set (one dimensional) into json. Now, I need to create a multi-dimensional json data structure based on multiple tables. I never done this and I need some help.

My tables:

users (1 to many)
phones (many to 1)
addresses (many to 1)
names (many to 1)

I need to develop JSON response that has this structure for each row:

user_id: user_id
phones: phone1, phone2, phone3, etc.
addresses: address1, address2, address3, etc.
names: name1, name2, name3, etc.

How should I do it?

For example, I am thinking that I can create 3 queries for each table (phones, addresses, names) and return a data set for each table and then encode each result set into json. Now my questions: Will it be a correct way of doing this? How do I add each child json into my parent json?

$userJson = json_encode($user_id_resultSet);
$phonesJson = json_encode($phones_resultSet);
$addressesJson = json_encode($addresses_resultSet);
$namesJson = json_encode($names_resultSet);
//how do I combine them into one row?

are you talking about combining the results sets (doing array_merge) or creating some sort of query that returns one result set? — Andrew
– Andrew, Commented Jun 29, 2013 at 22:07

Axel Amthor · Accepted Answer · 2013-06-29 22:08:56Z

2

$combined = array(
  "user" => $user_id_resultSet,
  "phones" => $phones_resultSet,
  "addresses" => $addresses_resultSet,
  "names" => $names_resultSet
);

echo json_encode($combined, JSON_PRETTY_PRINT); // just for neat output.

answered Jun 29, 2013 at 22:08

Axel Amthor

11.1k1 gold badge28 silver badges45 bronze badges

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

2 Comments

Andrew Over a year ago

that is amazing. What about dong it in the loop? When I have multiple users (so there should be multiple rows?)

halfer Over a year ago

@Andrew - that's just basic array manipulation, give it a go! Try an empty array $users = array(); and then add new stuff to it with $users[] = array( ... );. You can then add $users to the combined array sent to the JSON encoder.

Collectives™ on Stack Overflow

Creating a multi-dimensional json based on mysql query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related