Recursive logic in PHP + MySQL

Question

I am trying to apply recursive logic.

I have the following data in the 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 for receiving data: if I receive data for July 2014 (the 7th month), then

• appointment_id(21) is retrieved (1 row) and ...

• appointment_id(18) weekly recursively, repetitions in July are selected (4 lines):

   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. The date changes because the assignment is recursive for each week, which means that I add 7 days to each date. 2014-06-02 + 7 days = 2014-06-09 and so on. Thus, for July, the date is 2014-07-07.

• appointment_id(19) is a monthly recursive, repeats in July (1 line) are repeated:

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

  Note. The date changes because the assignment is recursive for each month, which means that I add one month to the date.

Final output (6 lines 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 above requirements?

Answer 1

Using a query for each type of recursion merged together.

Weekly and monthly recursions use a pair of cross-linked queries to create a range of numbers to add to the date. This handles up to 1000 repeat meetings, but easily expands to a larger one (if appointments want to be repeated over 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' 

The SQL script for this is here: -

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