Dynamic column selection in MySQL

Question

I know how to transfer one MySQL query to another:

SELECT user_name FROM users WHERE user_id=( SELECT user_id FROM entries WHERE header="foo" ); 

Out of sheer intellectual curiosity, how do I dynamically select a column or table?

Example:

 SELECT ( SELECT column_name FROM column_names WHERE id = 1 ) FROM ( SELECT table_name FROM table_names WHERE id = 1 ); 

Answer 1

Use a prepared statement:

  mysql> SET @sql = CONCAT ("SELECT", (SELECT "NOW ()"));
 Query OK, 0 rows affected (0.00 sec)

 mysql> SELECT @sql;
 + -------------- +
 |  @sql |
 + -------------- +
 |  SELECT NOW () | 
 + -------------- +
 1 row in set (0.00 sec)

 mysql> PREPARE stmt FROM @sql;
 Query OK, 0 rows affected (0.00 sec)
 Statement prepared

 mysql> EXECUTE stmt;
 + --------------------- +
 |  NOW () |
 + --------------------- +
 |  2009-04-06 23:08:31 | 
 + --------------------- +
 1 row in set (0.00 sec)

Answer 2

I am sure this is not possible with a regular query or view.

Answer 3

In answer to your first question, you should learn how to make JOIN in SQL. Joining is a fundamental SQL operation. Understanding how to make a loop in other languages ​​is important.

 SELECT DISTINCT users.user_name FROM users JOIN entries USING (user_id) WHERE entries.header = 'foo'; 

As for the second question, no, you cannot dynamically change the names of tables or columns in a single statement.

However, you can write code in your application to create an SQL statement as a string based on a search for column names and table names. Then execute the resulting string as a new SQL query.

Answer 4

You can do this by querying the information_schema.columns table.

Do this and check the results. I'm not sure what you are trying to do, but this table contains everything related to your columns:

 SELECT * FROM information_schema.`COLUMNS` C; 

By the way, I do not know how to do this in one request. You need to get the column information and then create a new query in the coding language, whatever that is.