States and cities

[keith]

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]

I am not sure I understand the question....can you give more details on what you want?

[keith]

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]

Manually? You have the migration script to do this automatically...
Do you require a sql statement to do this manually in the database?

[keith]

I have the new modified script (purchased)

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

[admin2]

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