Mysql date_sub using field as interval

Question

I need help with mysql and date_sub (). I have a table call function

Activity(id,deadline,alert)
Activity(1,'2011-04-18','1 DAY');
Activity(2,'2011-04-13','1 MONTH');

Each line in has a “warning”, this field indicates how time to the deadline the event should report.

for instance

On 2011-04-17 I have to report the activity with 'id' 1
On 2011-03-14 I have to report the activity with 'id' 2

I am trying to use functions date_sub(), but I cannot use a field as parameters of this function. Any idea how to fix this?

SELECT * 
  FROM `activities` 
 WHERE date_sub(`deadline`, INTERVAL alert) >= CURDATE();

Answer 1

Divide the alert into 2 fields

Alert_count: integer
Alert_period: enum('hour','day','month','week')

And change the request like this:

SELECT * 
  FROM `activities` 
 WHERE CASE alert_period 
   WHEN 'hour' THEN date_sub(`deadline`, INTERVAL alert_count HOUR) >= CURDATE();
   WHEN 'day' THEN date_sub(`deadline`, INTERVAL alert_count DAY) >= CURDATE();
   ...
 END CASE

Answer 2

, case:

WHERE case 
      when alert = '1 DAY' then date_sub(`deadline`, INTERVAL 1 DAY) 
      when alert = '1 MONTH' then date_sub(`deadline`, INTERVAL 1 MONTH) 
      ... etc ...
      end >= CURDATE();

Answer 3

INTERVAL, .

Activity(id,deadline,alert)
Activity(1,'2011-04-18','1');
Activity(2,'2011-04-13','30'); 

:

SELECT * 
  FROM `activities` 
 WHERE date_sub(`deadline`, INTERVAL alert DAY) >= CURDATE();

Answer 4

, , , , MySQL , .

alert_date DATE, ( , ), MySQL :

SELECT id FROM activity WHERE alert=CURRENT_DATE();

( ):

SELECT id FROM activity WHERE alert="2011-04-23";