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

appointment_id(21) is fetched (1 row) and…
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.
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?

Is “recurrent” the word you want to use? “Recursive” sounds inappropriate in this context to me, but I am not a native English speaker. — Palec
– Palec, Commented Jul 9, 2014 at 10:18

Kickstart · Accepted Answer · 2014-07-08 13:00:58Z

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

answered Jul 8, 2014 at 13:00

Kickstart

21.5k2 gold badges26 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

DS9 Over a year ago

Thanks for your answer. can you explain little beat about, what is 10 WEEK and 10 MONTH do? and why you repeat SELECT 0 i UNION this 10 times? sorry,but i am not able to understand.

Kickstart Over a year ago

The 2 cross joined sub queries each generate a range of numbers from 0 to 9. As they are cross joined together they give every combination of the numbers (ie, 100 combinations). So using units.i + tens.i * 10 it generates the numbers from 0 to 100 (it can easily be expanded to cross join again to go to 1000, etc). This is then added as a number of weeks or number of months (depending on the query) to the starting date, hence giving up 100 repeated appointment date (as the first number is 0, that includes the first appointment).

Collectives™ on Stack Overflow

Recursive logic in PHP + MySQL

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related