MYSQL 'insert record' Loop over days and weeks

I am trying to automate the entering of records into a table, nothing too fancy table wise

ID (primary, auto increment)
UserID (foreign key to a user table)
Date (dd-mm-yyyy)
Notes (blank by default)

In a nutshell I want to write a small routine that will auto populate this table, preferably (for now) just using MYSQL, though this COULD be done in PHP if required

I want to take a date (for example Mon 2nd Feb 2015) and automate a 'routine' for a user by automatically adding records for a user ID which will form a 'on call' rota.

Example one group of users has a routine of

Week 2 - FRI, SAT. SUN
Week 3 - OFF
Week 4 - OFF
Week 5 - OFF
Week 6 - MON, TUE, WED, THU
Week 7 - FRI, SAT, SUN

This is rigid and the formula doesn't change so user 1 will follow the exact routine above user 2 will start the routine one week later.. i,e

Week 1 - OFF
Week 3 - FRI, SAT. SUN
Week 4 - OFF
Week 5 - OFF
Week 6 - OFF
Week 7 - MON, TUE, WED, THU

So in the instance of user one, it will add a record for

(Feb) 2nd, 3rd, 4th, 5th, 13th,14th,15th,
(Mar) 9th, 11th, 12th, 13th

If someone could demonstrate how this can be done (with just ONE user) then I will be able to use that knowledge to get started on the rest and the other rotas I need to generate (1 full week in 3 etc etc). With the addition of MYSQL SCHEDULES, this makes searching for help on this subject a bit tricky.

Many thanks for any help getting going on this area of MYSQL / Dates and Loops
Who is Participating?
chaauConnect With a Mentor Commented:
MySQL lacks recursive CTEs. You will not be able to do it with a single query. MySQL has support for Stored Procedures. In my opinion it will be easy to write one
mvwmailAuthor Commented:
And thats what I did, thanks, sorry for long delay in closure
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.