Results 1 to 3 of 3

Thread: Recurring dates

  1. #1
    Join Date
    Jan 2009
    Posts
    124

    Unanswered: Recurring dates

    I need to have a query which will insert records of a recurring dates for the next year i.i. every Tuesday and 1PM for twelve months.

    I found this query and wonder if anyone can help me adapt it.

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

    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

    This is from How to do recurring dates with php/MySQL - Stack Overflow

    Suggestions would be very much appreiated

    Nick

  2. #2
    Join Date
    Jan 2009
    Posts
    124
    OK I have customized this a bit and it works partially.

    Table:
    CREATE TABLE `date_formats` (
    `dfid` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `format` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
    `type` varchar(200) NOT NULL,
    `locked` tinyint(4) NOT NULL DEFAULT '0',
    PRIMARY KEY (`dfid`),
    UNIQUE KEY `formats` (`format`,`type`)
    ) ENGINE=MyISAM AUTO_INCREMENT=40 DEFAULT CHARSET=utf8;

    query:
    INSERT
    INTO content_Calendar (title_ca, desc_ca, start_ca)
    SELECT title_ca, desc_ca, start_ca + INTERVAL n WEEK
    FROM (
    SELECT 1 AS n
    UNION ALL
    SELECT 2 AS n
    UNION ALL
    SELECT 3 AS n
    ) q
    JOIN content_Calendar
    ON key_ca = 1

    This inserts three records but what I really need i to set a variable on the number of records inserted. I will make a form on my website which the client can specify the number of weeks this even will repeat.

  3. #3
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    I would create a stored procedure in the database. This will allow you to loop inserting the contents into the database. You can pass in a start and end date into the procedure and it will perform a loop inserting each time. This link shows an example of a stored procedure which loops: MySQL – Splitting fields IT Integrated Business Solutions
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •