2

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

Improve this question

2 Answers 2

Reset to default
2

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.

Improve this answer
2
  • Thank you much for the reply Segfault. I wanted to originally go the route you mentioned with the data, but the cities_visited data is user created from a from the client, the user enters any city they want (the list of cities is not pre-populated). I'm not really sure how best else to approach this
    – Chris M
    Commented Apr 19, 2013 at 22:09
  • Yes, that can be challenge. If you just accept whatever they give you then you'll end up with things like "LA" "L. A." and "Los Angeles" all three, heh. If I was designing it then I would grab a list of cities to prepopulate the cities table with, then use some jquery to autocomplete (tag style) in the city text input box on the page. If they give you a city that doesn't match anything you have already, ask them to double check it, insert it anyway, or flag it for an admin to check. Something like that.
    – Segfault
    Commented Apr 20, 2013 at 4:02
1

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

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.