mySQL Repeating Events Query

Question

There are some solutions, but i don't know how to change that for my table. So i hope someone could help me.

I have the following Table

╔═════╦═════════╦════════════╦════════════╦═══════════╗
║ UID ║  TITLE  ║   BEGIN    ║    END     ║ RECURRING ║
╠═════╬═════════╬════════════╬════════════╬═══════════╣
║   1 ║ event A ║ 1359741600 ║ 1359745200 ║ none      ║
║   1 ║ event B ║ 1359741600 ║          0 ║ daily     ║
║   1 ║ event C ║ 1359741600 ║          0 ║ weekly    ║
║   1 ║ event D ║ 1359741600 ║          0 ║ monthly   ║
║   1 ║ event E ║ 1359741600 ║          0 ║ yearly    ║
╚═════╩═════════╩════════════╩════════════╩═══════════╝

How can i now select every event from now on up to 7 days and also all recurring events in the next 7 days?

The following i've tried but not working very good and not finished.

SELECT 
  * 
FROM
  `tx_events_domain_model_event` 
WHERE 
  /* none recurring events in the next 7 days */
  (
    recuring = 'none' 
    AND (begin_date + begin_time) >= UNIX_TIMESTAMP(NOW()) 
    AND (end_date + end_time) <= UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL 7 DAY))
  ) 
  OR 
  /* Daily */
  recuring = 'daily' 
  OR 
  /* Weekly */
  (
    recuring = 'weekly' 
    AND DAYOFWEEK(NOW()) - 1 <= DAYOFWEEK(FROM_UNIXTIME(begin_date)) - 1
  ) 
  OR 
  /* Monthly */
  (recuring = 'monthly' 
  AND 

Answer 1

Here's something I've been playing with (and here it is as an sqlfiddle with some sample data)... not 100% sure about it, but it should grab the last 7 days of data. Note that I'm using MySQL DATETIME versus integer timestamps, but you should be able to convert that easily (for testing the query it was much easier to use string dates).

SELECT *
  FROM
    (SELECT 
      *, 
      CONCAT(YEAR(NOW()), '-', MONTH(NOW()), '-', DAY(start)) AS monthly,
      CONCAT(YEAR(NOW()), '-', MONTH(start), '-', DAY(start)) AS yearly
      FROM events
    ) tmp
  WHERE
    (
      (recurring = 'none')
      OR (recurring = 'daily')
      OR (recurring = 'weekly')
      OR (
        recurring = 'monthly'
        AND (
          (
            monthly >= NOW()
            AND monthly <= DATE_ADD(NOW(), INTERVAL 7 DAY)
          )
          OR (
            DATE_ADD(monthly, INTERVAL 1 MONTH) >= NOW()
            AND DATE_ADD(monthly, INTERVAL 1 MONTH) <= DATE_ADD(NOW(), INTERVAL 7 DAY)
          )
        )
      )
      OR (
        recurring = 'yearly'
        AND (
          (
            yearly >= NOW()
            AND yearly <= DATE_ADD(NOW(), INTERVAL 7 DAY)
          )
          OR (
            DATE_ADD(yearly, INTERVAL 1 YEAR) >= NOW()
            AND DATE_ADD(yearly, INTERVAL 1 YEAR) <= DATE_ADD(NOW(), INTERVAL 7 DAY)
          )
        )
      )
    )
    AND start <= NOW()
    AND (
      end IS NULL 
      OR end >= DATE_ADD(NOW(), INTERVAL 7 DAY)
    )