Sort out the three first occurence of an attribute

Question

Hi!

I'm a beginner in MySql. But I am creating a calendar application, where I have a database of future event posts. The event post have the attributes: id, title, startTime, endTime, startDate, repeat. Where the last attribute repeat, is if the event post shall be repeated weekly, monthly or none start by startDate.

I want a sql-query to sort out event posts where only the three first dates occurrunce in the database starting today? Example if we have event posts for the dates 14,16,19 in the database, than these are the only dates to be shown with their corresponding event posts. Other days after the three first dates shall not be showned.

Pls HELP ME

Answer 1

To get events for the next three non-sequential days, starting today, use:

SELECT x.*
  FROM (SELECT ep.*,
               CASE
                 WHEN DATE(@dt) = DATE(x.dt) THEN @rownum
                 ELSE @rownum := @rownum + 1
               END AS rank,
          FROM EVENT_POST ep
          JOIN (SELECT @rowrum := 0, @dt := NULL) r
         WHERE ep.startdate >= CURRENT_DATE
      ORDER BY t.startdate, t.starttime) x
 WHERE x.rank <= 3

To get events for the next three sequential days, starting today, use the DATE_ADD function:

SELECT ep.*
  FROM EVENT_POST ep
 WHERE ep.startdate BETWEEN DATE(NOW)
                        AND DATE_ADD(DATE(NOW), INTERVAL 3 DAY)