How to do recurring dates with php/MySQL

Question

I have a table:

table Event
- name
- description
- date (in format 2011-08-06 11:00:00)
- id

I need to create recurring events.

How can I best duplicate a row with mostly the same data (name, description) but change the date?

Say, if I have an event that occurs August 6th, I would like to create three more events on - August 13, 20, 27 (every Saturday)

Also, is this something better accomplished with a mySQL query? I am guessing so because of the format the date is in...but any advice would be appreciated.

Best practice to not store recurring events in the database as individual rows. See: github.com/bmoeskau/Extensible/blob/master/… — Ben in CA
– Ben in CA, Commented Nov 22, 2019 at 20:08

Quassnoi · Accepted Answer · 2011-08-02 22:41:50Z

MySQL lacks a generator functionality (which would allow to create arbitrary length recordsets from scratch), so if the number of records is fixed, you could do something like that:

INSERT
INTO    event (name, description, date)
SELECT  name, description, date + INTERVAL n WEEK
FROM    (
        SELECT  1 AS n
        UNION ALL
        SELECT  2 AS n
        UNION ALL
        SELECT  3 AS n
        ) q
JOIN    event
ON      id = $myid

As you can see, the query layout depends on the number of events to create, with pure SQL there is no way to parameterize it.

You, however, can create a stored procedure and just call INSERT in a loop:

CREATE PROCEDURE prc_fill_event (p_id INT, p_cnt INT)
BEGIN
        DECLARE _cnt INT;
        SET _cnt = 1;
        WHILE _cnt <= p_cnt DO
                INSERT
                INTO    event (name, description, date)
                SELECT  name, description, date + INTERVAL _cnt WEEK
                FROM    event
                WHERE   id = p_id;
                SET _cnt = _cnt + 1;
        END WHILE;
END

Marvo · Accepted Answer · 2011-08-02 22:40:39Z

1

Depends. If it's a recurring date that goes on into infinity, that's gonna be a lot of rows to create. Or you'd have to choose some limit on that, and that starts to complicate the problem.

Maybe it'd be better to have additional columns that describe recurring events, and then render them accordingly in your view layers.

answered Aug 2, 2011 at 22:40

Marvo

18.2k8 gold badges53 silver badges75 bronze badges

2 Comments

Stephen Over a year ago

Enter the event once in the DB, and let the Application layer know about recurring events (i.e. recurring every X Days, on every Xst of the month, etc, until X times, until X date, forever, etc).

redconservatory Over a year ago

I think I am going to limit it to something small(ish) like 10-20 times to make it less complicated.

Collectives™ on Stack Overflow

How to do recurring dates with php/MySQL

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related