Recursive logic in PHP + MySQL

Question

I am trying to apply recursive logic.

I have the following data in tbl_appointment table (recur_type: 1 = weekly, 2 = monthly, 0 = not recursive):

appointment_id    user_id    date        recur_type    .....
18                56         2014-06-02  1
19                56         2014-06-15  2
20                56         2014-06-20  0
21                56         2014-07-20  2

I have the following criteria to fetch data: If I fetch data for july 2014 (7th month) then

1. appointment_id(21) is fetched (1 row) and…

2. appointment_id(18) is weekly recursive, repetitions in july are fetched (4 rows):

   appointment_id    user_id    date        recur_type    .....
   18                56         2014-07-07  1
   18                56         2014-07-14  1
   18                56         2014-07-21  1
   18                56         2014-07-28  1
   

   Note: Date is changed because appointment is recursive for each week, which means I add 7 days to each date. 2014-06-02 + 7 days = 2014-06-09 and so on. Thus for july, date is 2014-07-07.

3. appointment_id(19) is monthly recursive, repetitions in july are fetched (1 row):

   appointment_id    user_id    date        recur_type    .....
   19                56         2014-07-15  2
   

   Note: Date is changed because appointment is recursive for each month, which means I add one month to the date.

Final output is (6 rows in total):

appointment_id    user_id    date        recur_type    .....
21                56         2014-07-20  2
18                56         2014-07-04  1
18                56         2014-07-11  1
18                56         2014-07-18  1
18                56         2014-07-15  1
19                56         2014-07-15  2

I tried the following code:

SELECT
    tu.email,
    ta.appointment_id,
    ta.user_id,
    ta.date,
    ta.time,
    ta.recur_type,
    0 recursive
FROM
    tbl_appointment ta
        LEFT JOIN
    tbl_user tu ON ta.user_id = tu.user_id
WHERE
    1 AND YEAR(ta.date) = '2014'
      AND MONTH(ta.date) = '06'
      AND ta.user_id = 56
UNION ALL SELECT
    tu.email,
    ta.appointment_id,
    ta.user_id,
    ta.date,
    ta.time,
    ta.recur_type,
    1 recursive
FROM
    tbl_appointment ta
        LEFT JOIN
    tbl_user tu ON ta.user_id = tu.user_id
WHERE
    1 AND recur_type = '2'
      AND ta.user_id = 56
UNION ALL SELECT
    tu.email,
    ta.appointment_id,
    ta.user_id,
    ta.date,
    ta.time,
    ta.recur_type,
    2 recursive
FROM
    tbl_appointment ta
        LEFT JOIN
    tbl_user tu ON ta.user_id = tu.user_id
WHERE
    1 AND recur_type = '1'
      AND ta.user_id = 56
ORDER BY date DESC, time

How to satisfy the requirements above?

Answer 1

Using a query for each recursion type, unioned together.

The weekly and monthly recursions use a couple of cross joined queries to generate a range of numbers to add to the date. This copes with up to 1000 repeated appointments but is easily expanded to more (if the appointments want to repeat for more than ~20 years ).

SELECT a.appoinemnt_id, a.user_id, a.recur_type, a.date AS appoint_date
FROM tbl_appointment a
WHERE a.recur_type = 0
HAVING appoint_date BETWEEN '2014-07-01' AND '2014-07-31'
UNION
SELECT a.appoinemnt_id, a.user_id, a.recur_type, DATE_ADD(a.date, INTERVAL units.i + tens.i * 10 WEEK) AS appoint_date
FROM tbl_appointment a
CROSS JOIN (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9)units
CROSS JOIN (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9)tens
WHERE a.recur_type = 1
HAVING appoint_date BETWEEN '2014-07-01' AND '2014-07-31'
UNION
SELECT a.appoinemnt_id, a.user_id, a.recur_type, DATE_ADD(a.date, INTERVAL units.i + tens.i * 10 MONTH) AS appoint_date
FROM tbl_appointment a
CROSS JOIN (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9)units
CROSS JOIN (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9)tens
WHERE a.recur_type = 2
HAVING appoint_date BETWEEN '2014-07-01' AND '2014-07-31'

SQL fiddle for this here:-

http://www.sqlfiddle.com/#!2/6346a2/2