php - Inserting data from user_table into currency_table -

i have 3 tables, currency_types, userdb, , user_currency.

userdb has currency fields (gold, sapphires, amethysts, garnets, , pkeys). want move data in currency fields user_currency table. user_currency table has fields currency_id, , value fields. currency_id field related currencytypes.id` field.

i thought making while loop each currency can't think of how finish , there must better way of doing not seeing.

i know mysql_* deprecated. site being recoded use mysqli there no point in mentioning it.

currency_types table:

sql query: select * `currency_types` limit 0, 25 ;  id  name 1   gold 2   sapphires 3   amethysts 4   garnets 5   keys 6   f. stones 7   silk 8   leather 9   copper 10  cotton 11  iron 12  potions 13  silver 14  brass 15  steel 16  adamantine 

user_currency table:

sql query: select * `user_currency` limit 0, 25 ;  id  user_id  currency_id  value 

userdb table:

sql query: select `id`, `gold`, `sapphires`, `amethysts`, `garnets`, `pkeys` `userdb` `id`=1 limit 0, 25 ;  id  gold    sapphires   amethysts   garnets pkeys 1   301518  1370        946         82      272 

php: (can't figure out how currency_id field)

$gold_query=mysql_query('select `id`, `gold` `userdb`'); while($gold=mysql_fetch_array($gold_query)) {     mysql_query('insert `user_currency` (`user_id`, `currency_id`, `value`)         select `id`, `gold`             `userdb`         `id`='.$user['id']); } 

a 1 time shot, no php loop or php @ all, run mysql gui or whatnot.

if want ones value=0, drop clause on each.

insert `user_currency` (`user_id`, `currency_id`, `value`) select id,'gold',gold userdb gold<>0;  insert `user_currency` (`user_id`, `currency_id`, `value`) select id,'sapphires',sapphires userdb sapphires<>0;  insert `user_currency` (`user_id`, `currency_id`, `value`) select id,'amethysts',amethysts userdb amethysts<>0;  insert `user_currency` (`user_id`, `currency_id`, `value`) select id,'garnets',garnets userdb garnets<>0;  insert `user_currency` (`user_id`, `currency_id`, `value`) select id,'pkeys',pkeys userdb pkeys<>0; 

it assumes user_currency.id auto_inc , therefore not provided in insert stmt.