Dynamic conversion of string into column name. MySQL

Question

I have two tables: items and orders

items
--------------
id (int) | type_1 (int) | type_2  (int)|

orders
--------------
id (int) | transaction_type enum ('type_1', 'type_2')

Basically, I want to do the following:

select (select transaction_type from orders where id=1) from items;

So, the problem is that string returned by select transaction_type from orders where id=1, cannot be converted into column name.

Answer 1

You may want to see the answer to this question, which I believe is what you're trying to accomplish. In short, the answer suggests using prepared statements in order to simulate an eval()-esque functionality. In your case, this may work (you can see the SQLFiddle here:

SELECT transaction_type FROM orders WHERE id=1 into @colname;
SET @table = 'items';
SET @query = CONCAT('SELECT ',@colname,' FROM ', @table);

PREPARE stmt FROM @query;
EXECUTE stmt;

I won't claim to be any sort of expert on the underlying mechanics at work, but per the comments it seems to achieve the goal. Again, this was adopted from another answer, so if it works makes sure to +1 that one :)