How to Insert a new record in new table for each row in existing table

[kyranagn10]

I have a mysql table with my customers subscriptions. For each year since 2005 i have to add a new column with the subscription amount

For avoiding to enter a new column to my table for every year, i need to create a new table with the format of

Because are too many subscribers, how i can add the subscriptions to a new table? I know how to create a SELECT query with INNER JOIN after that but i don’t know how to INSERT each column as a new record (row).

[m_hutley]

INSERT into table2(userid,year,amount) SELECT userid, "y2005", y2005 FROM table1
(repeat for each year. A bit of an annoyance, but theres a lot fewer years than subscribers.)

[mabismad]

The year column in the new table should not include the ‘y’ character.

Since you have posted this in the php forum, here’s a php solution. I would define an array with the year range, then loop over this to build and execute an INSERT … SELECT query.

Untested, but should work -

// define the numerical range of columns
$cols = range(2005,2025);

foreach($cols as $col)
{
	// use your actual table2 and table1 names
	$sql = "INSERT table2 (userid,year,amount) SELECT userid,'$col',y{$col} FROM table1";
	// execute the query using your favorite database extension
	$db->query($sql);
}

[m_hutley]

I dont know why i always forget that PHP implements range…