Author Topic: States and cities  (Read 2279 times)

keith

  • Veteran Member
  • *****
  • Posts: 500
  • Karma: +4/-0
    • MatchMeUK - Free Dating
States and cities
« on: December 28, 2007, 02:18:52 PM »
How would I copy/update mysql data across to etano, currently my new data database doesnt contain cities etc..


Table: user_details (dsb)
fk_user_id = state, city

to

Table: dsb_user_profiles (etano)
fk_user_id = f6_state, f6_city


Keith


admin2

  • Guest
Re: States and cities
« Reply #1 on: December 28, 2007, 05:43:13 PM »
I am not sure I understand the question....can you give more details on what you want?

keith

  • Veteran Member
  • *****
  • Posts: 500
  • Karma: +4/-0
    • MatchMeUK - Free Dating
Re: States and cities
« Reply #2 on: December 28, 2007, 05:49:36 PM »
Hi

I would like to copy across state and city values from user_details table (old dsb),  to the new etano's dsb_user_profiles table

this allowing states and cities to be displayed



admin2

  • Guest
Re: States and cities
« Reply #3 on: December 28, 2007, 05:51:36 PM »
Manually? You have the migration script to do this automatically...
Do you require a sql statement to do this manually in the database?

keith

  • Veteran Member
  • *****
  • Posts: 500
  • Karma: +4/-0
    • MatchMeUK - Free Dating
Re: States and cities
« Reply #4 on: December 28, 2007, 05:56:58 PM »
I have the new modified script (purchased)

The statement for SQL will be suffice, I will just run it in phpmyadmin


admin2

  • Guest
Re: States and cities
« Reply #5 on: December 29, 2007, 03:48:16 AM »
Assuming that the states have the same IDs in dsb_loc_states table in etano database as in your $accepted_states[?] array from DSB, this sql is for the states:
Code: [Select]
update `<etanodb>`.`dsb_user_profiles` a, `<dsbdb>`.`user_details` b set a.`f6_state`=b.`state` where a.`fk_user_id`=b.`fk_user_id`;
Otherwise you will have to manually do it for every state:
Code: [Select]
update `<etanodb>`.`dsb_user_profiles` a, `<dsbdb>`.`user_details` b set a.`f6_state`=<ID from dsb_loc_states> where a.`fk_user_id`=b.`fk_user_id` and b.state='ID from $accepted_states';(repeat the above for every state you have in $accepted_states).

For the cities you have to do it manually in 2 steps:
Make a list of all cities you have in DSB and insert them in dsb_loc_cities table in etano db.
Then for every city:
Code: [Select]
update `<etanodb>`.`dsb_user_profiles` a, `<dsbdb>`.`user_details` b set a.`f6_city`=<ID from dsb_loc_cities> where a.`fk_user_id`=b.`fk_user_id` and lower(b.city)=lower('ft. lauderdale');(repeat the above for every city).

That's because the cities in DSB are textfields, not IDs like the states or countries but in Etano they are IDs too.
You will have to repeat the sql query for variations of the city name: 'ft. lauderdale' / 'fort lauderdale', 'orlando' / ' or la ndo' , etc.
It's going to be fun :)