CODEWITHSUNDEEP
phpmysqljson
Chris M
Chris M

Reputation: 4115

Insert nested array from json response into mysql

I have the following JSON response received from the client:

{
    "name": "Joel",
    "cities_visited": [{
        "city1": "Chicago",
        "city2": "Seattle"
    }],
    "active": true
}

I am using json_decode to get the results but having problems inserting the cities_visited as one single set of data into mysql. I have also tried with json_decode($json, true) with no luck.

Can anyone give me some insight how to get this data formatted properly prepared so i can insert into my db?

I need to somehow get this cities_visited array into one entry row on mysql and then be able to get it out and return it as the same with json_encode

Upvotes: 2

Views: 1087

Answers (2)

Segfault
Segfault

Reputation: 8290

If you have only one column for cities_visited and you need to put multiple values into it, then they need to be serialized somehow. Since you're dealing with JSON already you might as well turn it back into json to send it to the database.

$user = json_decode($input);
$user->cities_visited = json_encode($user->cities_visited);

Something like that. This isn't the best solution though, what you really want to do is create a many-to-man relationship between users and cities

Users

  • id (int)
  • name (string)
  • status (string)

Cities

  • id (int)
  • name (string)

Visits

  • user_id (int)
  • city_id (int)

And so in the example data you have above, you would create one row in the user table for Joel, two rows in the City table for Chicago and Seattle, and two rows in the Visits table that link them together.

The advantage to this approach is that it is much easier to analyze the data now. Let's say you want to know what city has been visited the most? Simply

select count(*) AS number_visits, name as city_name
from Visits v 
join Cities c on v.city_id = c.id
group by v.city_id
order by visits
limit 1

If the cities_visited field is serialized in the database, you have to loop through each user with PHP and count the results up yourself.

Upvotes: 2

Christian-G
Christian-G

Reputation: 2361

Since you are dealing with an array you can try to serialize the result before you store it in the database. Then unserialize it when you retrieve it.

See: http://php.net/manual/en/function.serialize.php

Upvotes: 1

Related Questions