MySQL selects column name as field

Question

I have a mysql table that looks something like this:

id | col_1 | col_2 | col_3 ---|-------|-------|------ 1 | 2 | 34 | 64 2 | 6 | 53 | 23 

I would like to be able to request an identifier and get multiple rows, one for each column. For example:

 SELECT column_name as column, column_value as value FROM my_table WHERE id=1; 

Which would give me:

 column | value -------|------- col_1 | 2 col_2 | 34 col_3 | 64 

What should I use to formulate such a request?

Many thanks

Answer 1

This is called a rod. In fact, this is the reverse rod. See here for some background. http://www.artfulsoftware.com/infotree/queries.php#78

MySQL makes this hard. This is a pain in the neck. Many people who work a lot in MySQL use programs to generate these queries.

 SELECT `column`, column_value FROM ( SELECT id, 'col_1' as `column`, col_1 as column_value FROM tab UNION SELECT id, 'col_2' as `column`, col_2 as column_value FROM tab UNION SELECT id, 'col_3' as `column`, col_3 as column_value FROM tab ) pivot WHERE id=1 

Answer 2

You can do it like this, it will return you 2 commas separated by the first of the columns of the second value, which you can explode and merge into KEY / VALUE arrays in PHP.

 SELECT GROUP_CONCAT(COLUMN_NAME) AS _columns, (SELECT GROUP_CONCAT(columnName, ',', columnName) FROM table_name WHERE id = 1) FROM information_schema.columns WHERE table_name = 'table_name' AND COLUMN_NAME IN ('columnName' , 'columnName');