title: MySQL Calendar with reminders
body: <p>I'm developing an ajax web calendar with possibility to schedule events and reminders.</p>
  
  <p><strong>The Schema</strong></p>
  
  <pre><code>id INT
  title VARCHAR
  startdate TIMESTAMP
  endtime TIME
  enddate DATE
  kind INT
  reminder INT DEFAULT 0
  </code></pre>
  
  <p><strong>The Explanation</strong></p>
  
  <p>"kind" field could be 0,1,2,3,4,5 as "one time","daily","workdays","weekly","monthly","yearly"</p>
  
  <p>"reminder" field is in minutes and means how many minutes before <code>startdate</code> the reminder should start to remind</p>
  
  <p>I need to select events "on going" and reminders (there is no problem to mix both... I will split kind "real event"/"reminder" in php)</p>
  
  <p><strong>The Select</strong></p>
  
  <p>In this SELECT I will only consider "one time" (0) and "daily" (1) kind of schedules... (easier to let you understand)</p>
  
  <pre><code>SELECT * 
  FROM calendar 
  WHERE
      enddate &gt;= NOW()
  AND
  (
      (
          NOW() &gt;= startdate
          AND kind=0
          AND CURDATE() = DATE(startdate)
          AND CURTIME() &lt;= endtime
      )
      OR
      (
          NOW() &gt;= startdate
          AND kind=1
          AND CURTIME() &gt;= TIME(startdate)
          AND CURTIME() &lt;= endtime
      )
      OR
      ( 
          NOW() &gt;= DATE_SUB(startdate, INTERVAL reminder MINUTE)
          AND NOW() &lt; startdate 
      )
  )
  </code></pre>
  
  <p><strong>The Problem</strong></p>
  
  <p>I have no problem selecting events and reminder of "one time" kind, because reminder acts <code>**reminder**</code> minutes before <code>**startdate**</code> thru <strong>DATE_SUB(startdate, INTERVAL reminder MINUTE)</strong></p>
  
  <p>As you can see, the SELECT miss the reminders for kind=1 after the first schedule, because repetition means "every day between startdate end enddate", and reminder itself should repeat to appear. </p>
  
  <p>I know I need to get the next schedule day/time and subtract <code>**reminder**</code>, but I didn't find the right solution till now... (remember that my "complete" SELECT should have workdays/weekly/monthly/yearly kind of schedules, than I need the right sql part to merge to other kind of schedules... but for this specific Question, I limit to daily reminders, just to not make difficult to understand what I am talking about! :D )</p>
  
  <p>How to solve this? Help me please :(</p>