CREATE TABLE `buz_usertype` ( `User_type` varchar(30) NOT NULL, `Type_id` tinyint NOT NULL AUTO_INCREMENT, PRIMARY KEY (`Type_id`) ); CREATE TABLE `users` ( `user_ID` mediumint unsigned NOT NULL AUTO_INCREMENT, `name` varchar(225) DEFAULT NULL, `lastname` varchar(255) DEFAULT NULL, `email` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `password` varchar(255) DEFAULT NULL, `usertype` tinyint unsigned NOT NULL, `Reg_date_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `registr_hash` char(32) DEFAULT NULL, `active` tinyint unsigned NOT NULL DEFAULT '0', `reset_pass_token` char(40) DEFAULT NULL, `token_expire` int unsigned DEFAULT NULL, `fb_user_ID` bigint DEFAULT NULL, `fb_user` tinyint unsigned DEFAULT '0', `del_account_time` timestamp NULL DEFAULT NULL, PRIMARY KEY (`user_ID`), UNIQUE KEY `email_UNIQUE` (`email`), KEY `fk_users_user_type1_idx` (`usertype`) ); CREATE TABLE `business_users` ( `bus_user_ID` mediumint unsigned NOT NULL AUTO_INCREMENT, `user_ID` mediumint unsigned NOT NULL, `comp_name` varchar(255) DEFAULT NULL, `address` varchar(100) DEFAULT NULL, `url` varchar(2083) DEFAULT NULL, `phone` varchar(20) DEFAULT NULL, `city` varchar(50) DEFAULT NULL, `municipality` varchar(100) DEFAULT NULL, `bus_user_type` tinyint NOT NULL, `same_d_appt` tinyint(1) NOT NULL DEFAULT '0', `f_words` tinytext, `apps_timeslot` tinyint unsigned DEFAULT NULL, `pack_selected` tinyint unsigned NOT NULL, `sched_entered` tinyint(1) NOT NULL DEFAULT '0', `holiday_closed` tinyint(1) NOT NULL DEFAULT '1', `staff_engage_in_appt` tinyint(1) DEFAULT NULL, `services_entered` tinyint(1) NOT NULL DEFAULT '0', `staff_entered` tinyint(1) DEFAULT NULL, `business_user_img_path` varchar(100) DEFAULT NULL, PRIMARY KEY (`bus_user_ID`), UNIQUE KEY `user_ID` (`user_ID`) ); CREATE TABLE `appointments` ( `apID` int unsigned NOT NULL AUTO_INCREMENT, `Bookfrom` varchar(45) DEFAULT NULL, `bookedfor` mediumint unsigned DEFAULT NULL, `appont_close_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `startDate` date NOT NULL, `startime` time NOT NULL, `endDate` date DEFAULT NULL, `endTime` time DEFAULT NULL, `apps_origin` enum('frontend','backend') NOT NULL, `delete_back` tinyint unsigned DEFAULT NULL, `delete_front` tinyint(1) DEFAULT NULL, `bookfromID` mediumint unsigned DEFAULT NULL, PRIMARY KEY (`apID`) ); CREATE TABLE `staff_list` ( `staff_ID` int unsigned NOT NULL AUTO_INCREMENT, `st_name` varchar(255) NOT NULL, `business_user_id` mediumint unsigned NOT NULL, `staff_image_path` varchar(100) DEFAULT NULL, PRIMARY KEY (`staff_ID`) ); CREATE TABLE `services_list` ( `serviceID` int NOT NULL AUTO_INCREMENT, `price` decimal(5,2) DEFAULT NULL, `servicename` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `duration` smallint unsigned DEFAULT NULL, `prices_visib` tinyint(1) DEFAULT NULL, `business_user_id` mediumint unsigned NOT NULL, PRIMARY KEY (`serviceID`) ); CREATE TABLE `appoint_servi_chosen` ( `app_ID` int unsigned NOT NULL, `service_ID` int NOT NULL, PRIMARY KEY (`app_ID`,`service_ID`) ); CREATE TABLE `appoint_staff_chosen` ( `app_ID` int unsigned NOT NULL, `staff_id` int unsigned NOT NULL, PRIMARY KEY (`app_ID`,`staff_id`) ); INSERT INTO `appointments` (`apID`,`Bookfrom`,`bookedfor`,`appont_close_time`,`startDate`,`startime`,`endDate`,`endTime`,`apps_origin`,`delete_back`,`delete_front`,`bookfromID`) VALUES (103,'giannis',267,'2021-11-06 11:19:44','2021-11-18','10:00:00','2021-11-18','11:00:00','backend',NULL,NULL,NULL); INSERT INTO `appointments` (`apID`,`Bookfrom`,`bookedfor`,`appont_close_time`,`startDate`,`startime`,`endDate`,`endTime`,`apps_origin`,`delete_back`,`delete_front`,`bookfromID`) VALUES (104,'nikos',267,'2021-11-06 11:19:44','2021-11-16','10:00:00','2021-11-16','11:00:00','frontend',NULL,NULL,'191'); INSERT INTO `services_list` (`serviceID`,`price`,`servicename`,`duration`,`prices_visib`,`business_user_id`) VALUES (44,NULL,'haircut',30,NULL,267); INSERT INTO `services_list` (`serviceID`,`price`,`servicename`,`duration`,`prices_visib`,`business_user_id`) VALUES (45,NULL,'haircut',NULL,NULL,272); INSERT INTO `services_list` (`serviceID`,`price`,`servicename`,`duration`,`prices_visib`,`business_user_id`) VALUES (46,NULL,'other',NULL,NULL,270); INSERT INTO `services_list` (`serviceID`,`price`,`servicename`,`duration`,`prices_visib`,`business_user_id`) VALUES (48,NULL,'pediciur',30,NULL,267); INSERT INTO `staff_list` (`staff_ID`,`st_name`,`business_user_id`,`staff_image_path`) VALUES (14,'eleni',270,NULL); INSERT INTO `appoint_servi_chosen` (`app_ID`,`service_ID`) VALUES (104,44); INSERT INTO `appoint_servi_chosen` (`app_ID`,`service_ID`) VALUES (104,48); INSERT INTO `appoint_staff_chosen` (`app_ID`,`staff_id`) VALUES (104,14); INSERT INTO `users` (`user_ID`,`name`,`lastname`,`email`,`password`,`usertype`,`Reg_date_time`,`registr_hash`,`active`,`reset_pass_token`,`token_expire`,`fb_user_ID`,`fb_user`,`del_account_time`) VALUES (191,'Dimitris','mitropoulos','foryou1911@gmail.com','$2y$10$F4jDcuLi3CTahzxTfetIwOINukqey90ZlQRt6CmAZrBMJE2DfvxgS',1,'2021-09-05 17:44:48','8d5e957f297893487bd98fa830fa6413',1,NULL,NULL,NULL,0,NULL); INSERT INTO `users` (`user_ID`,`name`,`lastname`,`email`,`password`,`usertype`,`Reg_date_time`,`registr_hash`,`active`,`reset_pass_token`,`token_expire`,`fb_user_ID`,`fb_user`,`del_account_time`) VALUES (154,'Dimitris','Παπαγεωριου','papageorgiou40@hotmail.com','$2y$10$6t1fPDeHVQCl2x3yp0JIXuHbDzHDYg8PUviz9rPTpRc3rpegm5Gam',2,'2021-06-30 13:56:59','da4fb5c6e93e74d3df8527599fa62642',1,NULL,NULL,NULL,0,NULL); INSERT INTO `business_users` (`bus_user_ID`,`user_ID`,`comp_name`,`address`,`url`,`phone`,`city`,`municipality`,`bus_user_type`,`same_d_appt`,`f_words`,`apps_timeslot`,`pack_selected`,`sched_entered`,`holiday_closed`,`staff_engage_in_appt`,`services_entered`,`staff_entered`,`business_user_img_path`) VALUES (267,154,NULL,'Filoproodon 12st',NULL,'6973999099','Athens',NULL,1,1,NULL,NULL,1,1,1,NULL,1,NULL,NULL); INSERT INTO `buz_usertype` (`User_type`,`Type_id`) VALUES ('hairStylist',1); SELECT * FROM appointments; SELECT * FROM services_list; SELECT * FROM appoint_servi_chosen; SELECT IF(business_users.comp_name IS NULL,CONCAT(users.name," ", users.lastname),business_users.comp_name) AS name, startDate,startime,User_type,apID FROM appointments INNER JOIN business_users ON appointments.bookedfor = business_users.bus_user_ID INNER JOIN users ON business_users.user_ID=users.user_ID INNER JOIN buz_usertype ON business_users.bus_user_type=buz_usertype.Type_id WHERE apps_origin ="frontend" AND delete_front is null AND bookfromID=(SELECT user_ID from users WHERE users.email='foryou1911@gmail.com'); SELECT serviceID,servicename from services_list INNER JOIN appoint_servi_chosen ON appoint_servi_chosen.service_ID=services_list.serviceID AND app_ID='104'; --SELECT * FROM staff_list; --SELECT * FROM appoint_staff_chosen; --SELECT * FROM business_users; --SELECT * FROM users; --SELECT * FROM buz_usertype; --queries follow for getting appointments from the db and services associated with an appt --QUERY for appt