MySQL Forums
Forum List  »  PHP

storing services for a specific appointment
Posted by: Dimitris Papageorgiou
Date: January 14, 2015 05:37AM

I am building a calendar where the user will be able to store appointments along with the services every appointment is associated with.

So...there is an appointments table:
CREATE TABLE `appointments` (
`apID` int(11) unsigned NOT NULL AUTO_INCREMENT,
`Bookfrom` varchar(45) DEFAULT NULL COMMENT 'σε αυτή την στήλη είναι όσοι κλεινουν το ραντεβού από το ιντερνετ και μη...όσοι είναι απο το ιντερνετ, registered δηλαδή, θα εμφανίζεται το ραντεβού στο frontend στην οθόνη τους',
`bookedfor` mediumint(11) unsigned DEFAULT NULL COMMENT 'σε αυτή την στήλη αναγράφονται τα ραντεβού για τον οποίον κλείνονται τα ραντεβού και εμφανίζονται στο backend του bookedfor business_user',
`appont_close_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`startDate` int(45) NOT NULL,
`endDate` int(45) NOT NULL,
`apps_origin` enum('frontend','backend') NOT NULL,
`staffID` int(11) unsigned DEFAULT NULL,
PRIMARY KEY (`apID`),
KEY `fk_appointments_user1_idx` (`Bookfrom`),
KEY `bokkedfor` (`bookedfor`),
KEY `fk_appointments_staff1_idx` (`staffID`),
CONSTRAINT `appointments_ibfk_2` FOREIGN KEY (`bookedfor`) REFERENCES `users` (`user_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

and there is a table where the services are stored and are specific to each appointemnt booked:
CREATE TABLE `appoint_servi_chosen` (
`app_ID` int(11) unsigned NOT NULL,
`service_ID` int(11) NOT NULL,
PRIMARY KEY (`app_ID`,`service_ID`),
KEY `fk_appoint_servi_chosen_services_list1_idx` (`service_ID`),
CONSTRAINT `appoint_servi_chosen_ibfk_1` FOREIGN KEY (`app_ID`) REFERENCES `appointments` (`apID`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_appoint_servi_chosen_services_list1` FOREIGN KEY (`service_ID`) REFERENCES `services_list` (`serviceID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

My problem has to do when adding/removing services to an already booked appointment.

When an appointments is booked for the first time I use a foreach loop where in it an INSERT statement puts the services associated with an appointment to the appoint_servi_chosen table...here is the loop(note that the variable services is an array holding the id of a service(taken from another table)):
foreach ($services as $value){
$upservice=$connection->query('INSERT into appoint_servi_chosen (service_ID,app_ID) values("'. $value.'","'.$id.'")');}

My problem is that when services are added/removed from existing appointments the above query will not work....an update will work...so the question is what algorithm should I use to catch all these scenarios

I have some ideas on my own but I would like to hear what do you have to say also.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.