php - Insert nested array from json response into mysql -

i have following json response received client:

{     "name": "joel",     "cities_visited": [{         "city1": "chicago",         "city2": "seattle"     }],     "active": true } 

i using json_decode results having problems inserting cities_visited 1 single set of data mysql. have tried json_decode($json, true) no luck.

can give me insight how data formatted prepared can insert db?

i need somehow cities_visited array 1 entry row on mysql , able out , return same json_encode

if have 1 column cities_visited , need put multiple values it, need serialized somehow. since you're dealing json might turn json send database.

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

something that. isn't best solution though, want create many-to-man relationship between users , cities

users

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

cities

• id (int)
• name (string)

visits

• user_id (int)
• city_id (int)

and in example data have above, create 1 row in user table joel, 2 rows in city table chicago , seattle, , 2 rows in visits table link them together.

the advantage approach is easier analyze data now. let's want know city has been visited most? simply

select count(*) number_visits, name city_name visits v  join cities c on v.city_id = c.id group v.city_id order visits limit 1 

if cities_visited field serialized in database, have loop through each user php , count results yourself.