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.

One possibility is using a number generator. Will the input variables be always one particular day, one particular month, one particular year and so on.. ? — Madhur Bhaiya, Nov 16 '18 at 05:39
Yeah I understand that part; but at a time only **one** value will be input for these variables, right ? — Madhur Bhaiya, Nov 16 '18 at 05:42
I believe the `column1` is of date type, so the input should be in **YYYY-MM-DD** format instead. — Madhur Bhaiya, Nov 16 '18 at 05:47
Please clarify the datatype of the columns in the table. It would be better if you add `SHOW CREATE TABLE` results to the question. I am working out a solution, and its complexity will vary on this. — Madhur Bhaiya, Nov 16 '18 at 06:08
I am not talking about the JS side code. Have you already created the MySQL table ? What is its structure ? What are the datatypes defined on the **MySQL table columns** ? — Madhur Bhaiya, Nov 16 '18 at 06:30
I have no idea what you are actually asking for, what query or queries do you want to make? perhaps you could supply an example? All I see at the js fiddle is a UI to choose some parameters values. — Paul Maxwell, Nov 16 '18 at 06:54
@EzioAuditore why are you using `bigint` to store date and time values. You should use native MySQL datatypes such as `date` and `time`. — Madhur Bhaiya, Nov 17 '18 at 04:33
@EzioAuditore Here is one idea: https://www.db-fiddle.com/f/7jTgSLwaaVdGn5qQProJJK/0 ; Run it and check the results. I will prepare a formal answer in some time. But it will be basically utilizing `INSERT INTO .. SELECT` syntax. — Madhur Bhaiya, Nov 18 '18 at 17:53
@EzioAuditore do provide feedback on the posted answer. Thanks :) — Madhur Bhaiya, Nov 18 '18 at 20:00

Madhur Bhaiya · Accepted Answer · 2018-11-18T18:39:18.420

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;

Is it possible to auto create querys according specific values?

1 Answers1