Dynamic field name selection in MySQL query

Question

Came across this as a problem, and would like to know a solution as there will be one more join on the resulting data set. I was unable to find anything specific, however I am sure it can be done --

I am currently using fields (day1, day2, day3, etc), as well as (month1, month2, etc) as a flag for day/month of schedule in addition with some timezone conversion on two time fields (stored as time).

The problem is when it is Day 1 in one area, it may be Day 2, or Day 7, in another area. I need the query to be dynamic on the result as flagged --- example structure

SELECT day1, day2, day3,... FROM mytable

What I would like to do is :

SELECT `day`[DAY(NOW())] as thisday FROM mytable

The result would be, that no matter what the day was, it would select the appropriate field for that record and use one column for the result. This would allow for use of :

... HAVING thisday = 1

While I am able to join string data together using CONCAT(blah, blah2, blah3), I need similar functionality for creating a field name to select (not a new field, an existing field in the database)

Where no native command exists, I am open to creating a function that would handle this as well.

Thanks in advance.

If anyone understood what is asked here, I'd be grateful for the translation. — Your Common Sense
– Your Common Sense, Commented Mar 16, 2014 at 19:49
I will try and translate as best I can --- When building a select statement, i would like to dynamically select a field based on a calculated value on a row by row basis. CONCAT offers the capability to join multiple strings together, however it does not seem to allow for building the name of the field you wish to select Given a field is named "FOO1"... I would like to select that field (lets assume CONCAT works), as follows - SELECT CONCAT(FOO, 1) - in reality : CONCAT would join the two together and result would be FOO1, however it would not be the field name it would be the field values. — Dan
– Dan, Commented Mar 16, 2014 at 19:51

jgrocha · Accepted Answer · 2014-03-16 19:57:51Z

1

I think you can use the CASE statement to select the right column accordingly to some value.

SELECT CASE DAY(NOW()) WHEN 1 THEN day1 WHEN 2 THEN day2 ELSE day29 END as thisday FROM mytable

answered Mar 16, 2014 at 19:57

jgrocha

3,1121 gold badge30 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Dynamic field name selection in MySQL query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related