Ok so here is the example query:
SELECT DISTINCT id, amount FROM tbl_recurring_payment AS t
WHERE ADDDATE(t.start_date, INTERVAL (t.period) UPPER(t.unit)) = CURDATE());
The area in question is UPPER(t.unit) where I want that expr to be treated as a mysql keyword (possible values of this column are day, week, month, year). It throws an error since it cant use the expr as a keyword. Is there anything I can do to get this to work or should I just add a check for the unit and hardcode the keyword for each possible value of unit?
I really just need to know if its possible to use an column value as a keyword in a query string
NO
At first glance, my thought is that I would approach this with a CASE statement:
SELECT -- Is the DISTINCT really necessary???
id,
amount
FROM
tbl_recurring_payment AS T
WHERE
CASE t.unit
WHEN 'YEAR' THEN ADDDATE(t.start_date, INTERVAL t.period YEAR)
...
END = CURDATE()
You might be able to streamline the code a bit (for readability, not performance) by using the INTERVAL syntax without ADDDATE, but I don't use MySQL enough to know if that's possible. (...t.start_date + CASE t.unit WHEN 'YEAR' THEN INTERVAL t.period YEAR...)?
