Inserting data from user_table into currency_table

Question

I have three tables, currency_types, userdb, and user_currency.

userdb has currency fields (gold, sapphires, amethysts, garnets, and pkeys). I want to move the data in those currency fields into the user_currency table. But the user_currency table has the fields currency_id, and value fields. The currency_id field is related to the currencytypes.id` field.

I thought I could do it by making a while loop for each currency but I can't think of how to finish it and there must be a better way of doing it that I am just not seeing.

I know mysql_* is deprecated. The site is being recoded to use MySQLi so there is no point in mentioning it.

currency_types table:

SQL query: SELECT * FROM `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 * FROM `user_currency` LIMIT 0, 25 ;

id  user_id  currency_id  value

userdb table:

SQL query: SELECT `id`, `gold`, `sapphires`, `amethysts`, `garnets`, `pkeys` FROM `userdb` WHERE `id`=1 LIMIT 0, 25 ;

id  gold    sapphires   amethysts   garnets pkeys
1   301518  1370        946         82      272

PHP: (Can't figure out how to get the currency_id field)

$gold_query=mysql_query('SELECT `id`, `gold` FROM `userdb`');
while($gold=mysql_fetch_array($gold_query))
{
    mysql_query('INSERT INTO `user_currency` (`user_id`, `currency_id`, `value`)
        SELECT `id`, `gold`
            FROM `userdb`
        WHERE `id`='.$user['id']);
}

Answer 1

A one time shot, no PHP loop or PHP at all, run from a mysql GUI or whatnot.

If you want ones with value=0, drop the where clause on each.

INSERT INTO `user_currency` (`user_id`, `currency_id`, `value`)
select id,'gold',gold from userdb where gold<>0;

INSERT INTO `user_currency` (`user_id`, `currency_id`, `value`)
select id,'sapphires',sapphires from userdb where sapphires<>0;

INSERT INTO `user_currency` (`user_id`, `currency_id`, `value`)
select id,'amethysts',amethysts from userdb where amethysts<>0;

INSERT INTO `user_currency` (`user_id`, `currency_id`, `value`)
select id,'garnets',garnets from userdb where garnets<>0;

INSERT INTO `user_currency` (`user_id`, `currency_id`, `value`)
select id,'pkeys',pkeys from userdb where pkeys<>0;

It assumes user_currency.id is an auto_inc and therefore not provided in the insert stmt.