Solved

MYSQL 'insert record' Loop over days and weeks

Posted on 2014-12-16
2
61 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

724 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