<!-- --> > 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 > > > > <pre> > ✓ > > ✓ > > ✓ > > ✓ > > ✓ > > ✓ > > ✓ > > ✓ > > ✓ > > ✓ > > ✓ > > ✓ > > ✓ > > ✓ > > ✓ > > ✓ > > ✓ > > ✓ > > ✓ > > ✓ > > ✓ > > ✓ > > apID | Bookfrom | bookedfor | appont_close_time | startDate | startime | endDate | endTime | apps_origin | delete_back | delete_front | bookfromID > ---: | :------- | --------: | :------------------ | :--------- | :------- | :--------- | :------- | :---------- | ----------: | -----------: | ---------: > 103 | giannis | 267 | 2021-11-06 11:19:44 | 2021-11-18 | 10:00:00 | 2021-11-18 | 11:00:00 | backend | <em>null</em> | <em>null</em> | <em>null</em> > 104 | nikos | 267 | 2021-11-06 11:19:44 | 2021-11-16 | 10:00:00 | 2021-11-16 | 11:00:00 | frontend | <em>null</em> | <em>null</em> | 191 > > serviceID | price | servicename | duration | prices_visib | business_user_id > --------: | ----: | :---------- | -------: | -----------: | ---------------: > 44 | <em>null</em> | haircut | 30 | <em>null</em> | 267 > 45 | <em>null</em> | haircut | <em>null</em> | <em>null</em> | 272 > 46 | <em>null</em> | other | <em>null</em> | <em>null</em> | 270 > 48 | <em>null</em> | pediciur | 30 | <em>null</em> | 267 > > app_ID | service_ID > -----: | ---------: > 104 | 44 > 104 | 48 > > name | startDate | startime | User_type | apID > :------------------------------ | :--------- | :------- | :---------- | ---: > Dimitris Παπαγεωριου | 2021-11-16 | 10:00:00 | hairStylist | 104 > > serviceID | servicename > --------: | :---------- > 44 | haircut > 48 | pediciur > </pre> *db<>fiddle [here](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=05fc283a3674472737bffcaca98c2b4f)*