Solved

MYSQL 'insert record' Loop over days and weeks

Posted on 2014-12-16
2
60 Views
Last Modified: 2015-11-01
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 1 - MON,TUE,WED,THU
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 2 - MON,TUE,WED,THU
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
0
Comment
Question by:mvwmail
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 25

Accepted Solution

by:
chaau earned 500 total points
ID: 40503810
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
0
 
LVL 3

Author Closing Comment

by:mvwmail
ID: 41166764
And thats what I did, thanks, sorry for long delay in closure
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question