Syntax error Best way to store weekly event in MySQL?

Best way to store weekly event in MySQL?

Let us see the best way to store weekly events in MySQL. For that, first create a new table and include fields for every day as well.

mysql> create table WeeklyEventDemo
   -> (
   -> ID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   -> EventName varchar(20),
   -> Monday tinyint(1),
   -> Tuesday tinyint(1),
   -> Wednesday tinyint(1),
   -> Thursday tinyint(1),
   -> Friday tinyint(1),
   -> Saturday tinyint(1),
   -> Sunday tinyint(1),
   -> StartDate date,
   -> EndDate date
   -> );
Query OK, 0 rows affected (0.55 sec)

Insert some records in the table using insert command. The query is as follows −

mysql> insert into WeeklyEventDemo(EventName,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday,StartDate,EndDate) values('Event-1',0,0,0,0,1,0,0,'2018-03-21','2019-03-08');
Query OK, 1 row affected (0.14 sec)
mysql> insert into WeeklyEventDemo(EventName,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday,StartDate,EndDate) values('Event-2',0,0,0,0,0,1,0,'2018-03-21','2019-03-08');
Query OK, 1 row affected (0.16 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from WeeklyEventDemo;

The following is the output

+----+-----------+--------+---------+-----------+----------+--------+----------+--------+------------+------------+
| ID | EventName | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday | StartDate  | EndDate    |
+----+-----------+--------+---------+-----------+----------+--------+----------+--------+------------+------------+
|  1 | Event-1   | 0      | 0       | 0         | 0        | 1      | 0       | 0       | 2018-03-21 | 2019-03-08 |
|  2 | Event-2   | 0      | 0       | 0         | 0        | 0      | 1       | 0       | 2018-03-21 | 2019-03-08 |
+----+-----------+--------+---------+-----------+----------+--------+----------+--------+------------+------------+
2 rows in set (0.00 sec)

The following is the query to get events weekly in MySQL

mysql> select *from WeeklyEventDemo where Friday=1;

The following is the output

+----+-----------+--------+---------+-----------+----------+--------+----------+--------+------------+------------+
| ID | EventName | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday | StartDate  | EndDate    |
+----+-----------+--------+---------+-----------+----------+--------+----------+--------+------------+------------+
|  1 | Event-1   | 0      | 0       | 0         | 0        | 1      | 0        | 0      | 2018-03-21 | 2019-03-08 |
+----+-----------+--------+---------+-----------+----------+--------+----------+--------+------------+------------+
1 row in set (0.00 sec)
Updated on: 2019-07-30T22:30:25+05:30

258 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements