将嵌套数组从json响应插入mysql

Question

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

Answer 1

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.