CODEWITHSUNDEEP
mysql
Dan
Dan

Reputation: 43

Dynamic field name selection in MySQL query

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.

Upvotes: 1

Views: 312

Answers (1)

jgrocha
jgrocha

Reputation: 3052

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

Upvotes: 1

Related Questions