How to get a value from a column in which the column name is from a subquery?

Question

I would like to ask, is it even possible for MySQL to interpret the result of the subquery as a table column for selection, and not just for a row?

What I have are some tables in which some columns are dynamically created, and a table in which the created custom columns are stored.

In this particular implementation, I use this to store custom fields for custom articles for the content management system we are developing.

Here is an approximate sketch of a diagram.

custom_column ----------------------------------------------------------------- id field_name field_type field_display table_name ----------------------------------------------------------------- 1 news_author string Author news 2 news_body text Body news 3 news_heading title Heading news 4 name title Name testimonials 5 message text Message testimonials 

Given that all articles (news, reviews, etc.) have a corresponding custom field field_type = title ...

I would just like to specify a specific table / article and get only the article headings of all the entries by doing something like:

 SELECT (SELECT field_name FROM custom_columns WHERE field_type = "title" AND table_name = "testimonials") AS article_title FROM testimonials 

And get the result line by line:

 id article_title ----------------------- 1 John Doe 2 Mary Jane 3 Justin Case 

And not that MySQL gives me through the above query:

 id article_title ----------------------- 1 name 2 name 3 name 

I see that MySQL interprets name - the result of a subquery - as a string, not a column name to select.

Of course, I can do something like:

 SELECT *, (SELECT field_name FROM custom_columns WHERE field_type = "title" AND table_name = "testimonials") AS article_title FROM testimonials 

And manipulate the results to get what I want in the program, for example:

 $article_title = $row->{$row->article_title}; //I use PHP, and most times use mysql_fetch_object to fetch rows from a MySQL resource. 

But I want to avoid querying all useless columns that I don't need, and hopefully save resources. In the end, I just need one column from their number, which theoretically can be quite a lot.

I searched everywhere for a MySQL function that I might not have enough to make it work, but was never found. I hope someone can point me in the right direction or, even better, suggest a better solution if it exists.

Thanks!

EDIT: for those who may not be familiar with the PHP syntax above, here is an equivalent script, but using the syntax of a very common array instead of the PHP syntax for objects:

 $row[$row['article_title']]; 

Answer 1

The general rule is that anytime you need to dynamically look for a column in SQL (PHP is different), you should probably revise your schema. If you ask a question on how to do this, I am sure that you will find many SO'ers who want to help.

The only dynamic MySQL I have ever found has been demonstrated in this question , everything else is an option on this. Your $article_title = $row->{$row->article_title}; realistically is the best solution if you cannot know in advance the possible column names.

Answer 2

If you do something like:

 select table1.field, (select something from table2), ... from table1 

then the results of the subquery will be considered as a field. To process the results of a subquery in a table, do

 select table1.field, subquerytable.field ... from table1, (select field from table2) as subquerytable ... 

Answer 3

Try the following:

 SELECT id, (SELECT field_name FROM custom_columns WHERE field_type = "title" AND table_name = "testimonials") AS article_title FROM testimonials ORDER BY id ASC 

I could also work without id , but you have to try it.

NTN

Answer 4

you can use EXECUTE for this DECLARE TabName VARCHAR (32); - To get the table name, select table_name in tabName from custom_columns, where ..... - create a query string set @qry = CONCAT ("SELECT * FROM", tabName, "...."); --Follow the request PREPARE stmt FROM @s; EXECUTE stmt; ...