In MySQL, combine a query to get column name with a second query on that column

Question

I want to use field name to determine the column that I will use to search for, but first I have to use the name to look up a column ID, then concatenate the column ID with a string to get the actual column name. I'm doing with with two queries now, but I'd like to combine them into a single query.

For example, I want to search for "fool=true". In the first table, I find out that values for "fool" are in column 1 of the second table. In the second table, I do the real search, for "field_id_1=true".

This table maps names to column IDs used in the second table.

exp_channel_fields
+field_id +field_name +
|---------|-----------|
|1        |fool       |
|2        |money      |
+---------+-----------|

This table has the data I want to search, but the column names don't quite match the ID from the prior step. I have to prepend "field_id_" to the ID I found before:

exp_channel_data
+entry_id+field_id_1 +field_id_2 +
|--------|-----------|-----------|
|837     |true       |$500       |
|838     |false      |$50,000    |
+--------+-----------+-----------+

Here's the first query I use, and I store the result into $myFieldID.

SELECT field_id as tField_id FROM exp_channel_fields 
WHERE field_name = 'fool'

Then I use the result to build the second query:

SELECT
    GROUP_CONCAT(entry_id ORDER BY entry_id SEPARATOR '|')
    AS result_entries
FROM exp_channel_data
WHERE field_id_{$myFieldID} = "true"

Is there a way to combine these into a single query? Thanks in advance!

i think it is not possible to do so: http://stackoverflow.com/questions/985842/mysql-concat-to-create-column-names — phoet, Nov 13 '11 at 15:56
I'll look into server prepared statements. That would at least eliminate the PHP code I'm using now to use the values returned from one query to construct the second query. — Mike3d0g, Nov 13 '11 at 18:54
@phoet, Your comment was the most helpful answer. One, it's not possible to directly do what I asked. Two, the link to server prepared statements *almost* provided the workaround I needed. (Alas, ExpressionEngine blocks server prepared statements.) Would you like to repost your comment as an answer? — Mike3d0g, Nov 22 '11 at 16:01

score 1 · Accepted Answer · edited May 23 '17 at 10:34

1

it is not possible to use query-results as column names, but you can use server-side prepared statements to achieve a similar behavior. have a look at this question:

MySQL concat() to create column names to be used in a query?

edited May 23 '17 at 10:34

Community

1
1

answered Nov 22 '11 at 18:24

phoet

18,688
4
46
74

score 0 · Answer 2 · answered Nov 13 '11 at 15:39

Your database is not right normalized. You should unpivot columns to rows in order to make this kind of querys:

exp_channel_fields
+field_id +field_name +
|---------|-----------|
|1        |fool       |
|2        |money      |
+---------+-----------|


exp_channel_data
+entry_id+field_id + value
|--------|-----------|-----------|
|837     |1          |true       |
|837     |2          |$500       |
|838     |1          |False      |
|838     |2          |$50,000    |
+--------+-----------+-----------+

Then the query is really easy:

SELECT field_id as tField_id FROM exp_channel_fields WHERE field_name = 'fool'

SELECT
    GROUP_CONCAT(entry_id ORDER BY entry_id SEPARATOR '|')
    AS result_entries
FROM 
   exp_channel_data inner join 
   exp_channel_fields 
     on exp_channel_data.field_id = exp_channel_data.field_id
WHERE value = "true"

Perhaps you will need table-base and sub-tables for exp_channel_fields because differents value types.

Unfortunately, I can't change the tables. This is the way they're laid out in ExpressionEngine CMS. Thanks, though for the example. I'll keep it in mind for future projects. — Mike3d0g, Nov 13 '11 at 15:43

In MySQL, combine a query to get column name with a second query on that column

2 Answers2