Is there any way to create multiple new columns for a table based on result set with mysql script

Question

I want to create a new column for each language type in a language table.

so each language can be found with: select code from languages;

For each language code I want a new column in another table..

Manually I would run

alter table blah add column text_en_GB; alter table blah add column text_fr_FR; ...

I hoped I could build queries using: select concat ("alter table blah add column text_", code, ";\n") from languages; then prepare and execute this but that doesn't work with a block of statements. :(

Answer 1

Found a better way :-)

SET SESSION group_concat_max_len = 1000000;
SELECT CONCAT('ALTER TABLE blah ', GROUP_CONCAT( CONCAT('ADD COLUMN `blah_name_', `language_code`, '` varchar(255) NOT NULL') SEPARATOR ", "), ";" ) FROM `language` INTO @poo;
PREPARE stmt1 FROM @poo;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;