Is it possible to auto create querys according specific values?

Question

Is it possible to create multiple querys choosing the day of week?

MYSQL query:

create table reservations (
  id bigint(20) NOT NULL,
  var_start datetime NOT NULL, 
  var_end  datetime NOT NULL
)

For example

var_day   = "3"   // Wednesday 
var_month = "11"  // November 
var_year  = "2018" 
var_start = "11:00”   // 11 am
var_end   = "13:00”  // 1 pm

//This will create all inserts according all wednesday on november 2018.
insert into table var_start = "07-11-2018 11:00:00" var_end = "07-11-2018 13:00:00"
insert into table var_start = "14-11-2018 11:00:00" var_end = "07-11-2018 13:00:00”
insert into table var_start = "21-11-2018 11:00:00" var_end = "07-11-2018 13:00:00”
insert into table var_start = "28-11-2018 11:00:00" var_end = "07-11-2018 13:00:00”

Here is the html demo

I will appreciate some links or concepts to search the correct information about automatic querys or this kind of concepts.

Thanks in advance.

Answer 1

We will be basically dynamically generating all the required dates within the query itself. And then use that result-set to Insert into the reservations table.

I have changed id column to Primary Key and Auto Increment (as it should be).

In a Derived table, we will use a number generator from 0 to 4, as there can be at maximum 5 Wednesdays (and other weekdays) in a month.

Now we will try to get the first Sunday of the required month. For that, we will firstly create a date corresponding to first date of the month, using the input variable values for the month and year:

STR_TO_DATE(CONCAT('2018','11','01'), '%Y%c%d')

Concat('2018','11','01') basically generates 20181101 string. We can then use Str_to_date() function to convert it into MySQL date format. We could have use Concat() function directly to get in YYYY-MM-DD format; but this approach should be robust in case the input month is 9 instead of 09.

Now, we will use various Datetime functions to determine the nth Wednesday. I have expanded over the answer originally given here: https://stackoverflow.com/a/13405764/2469308

Number generator table will help us in calculating the 1st, 2nd, 3rd Wednesday and so on.. We can basically get the first Wednesday by adding 3 number of days to the first Sunday. Afterwards, we will basically add 7 days everytime to get next Wednesday in the month.

Eventually, we will use all these dates and AddTime() to them for determining var_start and var_end accordingly. Also, there is a possibility that in the 5th day, it may cross to next month. So we will filter those out using WHERE MONTH(..) .. AND YEAR(..) .. conditions.

Finally, INSERT INTO.. SELECT statement will be used to insert into the reservations table.

Schema (MySQL v5.7) View on DB Fiddle

create table reservations (
  id bigint(20) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  var_start datetime NOT NULL, 
  var_end  datetime NOT NULL
);

/*
var_day   = "3"   // Wednesday 
var_month = "11"  // November 
var_year  = "2018" 
var_start = "11:00”   // 11 am
var_end   = "13:00”  // 1 pm
*/

---

Query #1

INSERT INTO reservations (var_start, var_end)
SELECT
  ADDTIME(dates.nth_date, '11:00') AS var_start, 
  ADDTIME(dates.nth_date, '13:00') AS var_end 
FROM 
(
SELECT
  STR_TO_DATE(CONCAT('2018','11','01'), '%Y%c%d') + 
  INTERVAL (6 - 
            WEEKDAY(STR_TO_DATE(CONCAT('2018','11','01'), '%Y%c%d')) + 
            3 + 
           (7*nth)) DAY AS nth_date 
FROM 
(SELECT 0 AS nth UNION ALL
 SELECT 1 UNION ALL
 SELECT 2 UNION ALL
 SELECT 3 UNION ALL
 SELECT 4) AS num_gen
) AS dates
WHERE MONTH(dates.nth_date) = 11 AND 
      YEAR(dates.nth_date) = 2018;

---

Query #2

SELECT * FROM reservations;

| id  | var_start           | var_end             |
| --- | ------------------- | ------------------- |
| 1   | 2018-11-07 11:00:00 | 2018-11-07 13:00:00 |
| 2   | 2018-11-14 11:00:00 | 2018-11-14 13:00:00 |
| 3   | 2018-11-21 11:00:00 | 2018-11-21 13:00:00 |
| 4   | 2018-11-28 11:00:00 | 2018-11-28 13:00:00 |

---

In terms of input variables (prefixed with : for parametric queries), the query would looks as follows:

INSERT INTO reservations (var_start, var_end)
SELECT
  ADDTIME(dates.nth_date, :var_start) AS var_start, 
  ADDTIME(dates.nth_date, :var_end) AS var_end 
FROM 
(
SELECT
  STR_TO_DATE(CONCAT(:var_year,:var_month,'01'), '%Y%c%d') + 
  INTERVAL (6 - 
            WEEKDAY(STR_TO_DATE(CONCAT(:var_year,:var_month,'01'), '%Y%c%d')) + 
            :var_day + 
           (7*nth)) DAY AS nth_date 
FROM 
(SELECT 0 AS nth UNION ALL
 SELECT 1 UNION ALL
 SELECT 2 UNION ALL
 SELECT 3 UNION ALL
 SELECT 4) AS num_gen
) AS dates
WHERE MONTH(dates.nth_date) = :var_month AND 
      YEAR(dates.nth_date) = :var_year;