database - Passing column name as parameter in MySQL

Question

Is it possible to do something like this:

SELECT template_id as id FROM templates WHERE ? IN template_allowed_parent_templates

So I want to see if a given ID is in the column. If not, how can I fix this with only MySQL?

Answer 1

As far as passing a column name as a parameter in MySQL (the question in the title), that's not possible in a SQL statement. The identifiers in a SQL statement (e.g. the table names and column names) must be part of the SQL text. Those can't be passed in with a bind placeholder.

The IN comparison operator expects a list of values enclosed in parens, or a SELECT query enclosed in parens that returns a set of values.

It's not clear what you are attempting to do.

I suspect that template_allowed_parent_templates is a column in the table, and it contains a comma separated list of values. (shudder.)

I suspect you might be looking for the MySQL FIND_IN_SET string function, which will "search" a comma separated list for a particular value.

As a simple demonstration:

  SELECT FIND_IN_SET('5', '2,3,5,7')
       , FIND_IN_SET('4', '2,3,5,7')

The function returns a positive integer when the specified value is "found". You can make use of that function in a predicate, e.g.

WHERE FIND_IN_SET(id,template_allowed_parent_templates)

or

WHERE FIND_IN_SET( ? ,template_allowed_parent_templates)

(This works because in MySQL a positive integer is evaluated as TRUE in a boolean context.)

I'm only guessing at what you are trying to accomplish.