Matt Penfold Matt Penfold - 6 months ago 15
MySQL Question

multiple entries from variable date range

i have a form on page1 that has 4 inputs (Name, startdate, enddate, reason).

i want to add an entry into MySQL for every day from the date range (startdate, enddate)

i know how to add to MySQL i just dont know how to get the process of getting different dates and then inserting them.

this needs to be automated as the date ranges change every time.

so it could be 04/07/2018 - 09/07/2018 or 01/02/2017 - 02/02/2017 depending on the users selection on Page1.

kinda as follows

$name = $_POST['name'];

$startdate = $_POST['startdate'];

$enddate = $_POST['enddate'];

$reason = $_POST['reason'];


INSERT INTO taken (`name`, `date`, `reason`) VALUES ('$name', '$date', '$reason')


Where startdate is 01/01/2016 and enddate is 05/01/2016 i want to add that 5 times for example

INSERT INTO taken (`name`, `date`, `reason`) VALUES ('Joe Bloggs', '01/01/2016', 'Holiday')

INSERT INTO taken (`name`, `date`, `reason`) VALUES ('Joe Bloggs', '02/01/2016', 'Holiday')

INSERT INTO taken (`name`, `date`, `reason`) VALUES ('Joe Bloggs', '03/01/2016', 'Holiday')

INSERT INTO taken (`name`, `date`, `reason`) VALUES ('Joe Bloggs', '04/01/2016', 'Holiday')

INSERT INTO taken (`name`, `date`, `reason`) VALUES ('Joe Bloggs', '05/01/2016', 'Holiday')


i have already searched and tried so many ways but none work or fail.

any help on this matter would be fantastic.

Answer

Create a table with all dates you will ever need (See here how to do that).

Change your taken.date column to DATE type.

Then you can do a simple bulk insert:

INSERT INTO `taken` (`name`, `date`, `reason`) 
    SELECT 'Joe Bloggs', d.`date`, 'Holiday'
    FROM `dates` d
    WHERE d.`date` BETWEEN '2016-01-01' AND '2016-01-05';
Comments