Solved

MYSQL 'insert record' Loop over days and weeks

Posted on 2014-12-16
2
55 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
2 Comments
 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
Comment Utility
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
Comment Utility
And thats what I did, thanks, sorry for long delay in closure
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

'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 …
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

728 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now