brad62 — 2013-02-07T13:56:05-05:00 — #1
I have a friend with pretty big db with users from all over the world.
The table have a lot of info about the users, like name, address, e-mail, country, and so on.
Now he wants to add one field to the db, listing what continent they live in.
So, all the people in France will have Europe added.
I'm not sure how I can do this the easiest way. Should I run a query for every country and change.
Or is it easier to make some kind of temporary web page where they are listed and I can click on the correct continent.
That would be pretty good, not sure how though, but if I had a list of the users sorted by country.
Like a table
NAME | COUNTRY | CONTINENT MENU LIST
Peter USA North America, South America, Europe, Asia,...
Michel France North America, South America, Europe, Asia,...
Then, when picking North America on the first one, all the others that have USA as country will be updated with the same continent.
Is that something that can be done or is there an easier way of doing it?
spacephoenix — 2013-02-07T14:33:06-05:00 — #2
For each time a country is listed in the database, are all the occurrences spelt the same?
cups — 2013-02-07T14:37:16-05:00 — #3
Countries by continent - will help you get a list, though if you are into sparql and dbpedia you can extract them as data.
I'd make a list of how many unique countries you have first, and really assure yourself that this is worth doing.
You could just make a table containing those countries, and match the continents to them.
You can then either JOIN the tables when "sort by continent is required", or run an update which fills in the holes in the main database from the continents database.
"UPDATE mytable set continent = "Europe" where country = "France";
cups — 2013-02-07T14:37:47-05:00 — #4
Ah, good one -- dirty data.
brad62 — 2013-02-07T14:53:09-05:00 — #5
Yes, all countries are selcted from a list, so they are spelled the same way.