there is one slight problem though…that complicates things.
The format in the columns in table A is not the same with the format in the columns table B.
This is table A…where the possible apps list is stored: CRE ATE TABLE avail_apps( idint(11) NOT NULL AUTO_INCREMENT, date_avdate NOT NULL, time_av time DEFAULT NULL, PRIMARY KEY (id,date_av) ) ENGINE=InnoDB AUTO_INCREMENT=8192 DEFAULT CHARSET=utf8
And here is table B…I am going to show only columns of interest:
CREATE TABLE appointments( … startDate datetime NOT NULL, endDate datetime NOT NULL,
I have fixed the issue with the formats…the query that does what I want is this:
SELECT date_av,time_av FROM avail_apps LEFT JOIN appointments ON avail_apps.date_av = appointments.startDate and avail_apps.time_av=appointments.startime WHERE startDate IS NULL and startime IS NULL;
It “catches” the available appointments slots.
There is one more detail left.
The appointments table is this: CREATE TABLE appointments( apIDint(11) unsigned NOT NULL AUTO_INCREMENT, bookedformediumint(11) unsigned DEFAULT NULL ', startDatedate NOT NULL, startimetime NOT NULL, endDatedate NOT NULL, endTime time NOT NULL, PRIMARY KEY (apID`),
Where is says _b it refers to the second hour zone where a store is open…
for example some stores close at noon and open again at afternoon…I just call that b zone.
How could possible bring into play this 3rd criteria…of course I am not sure the current table design(above) can help in that or maybe I must redesign this table in order to accomplish the greater goal
[quote=“designtrooper, post:9, topic:207497, full:true”]
…of course I am not sure the current table design(above) can help in that or maybe I must redesign this table in order to accomplish the greater goal
my initial thought is that you have a very clumsy structure here
what if some provider decides to have morning, afternoon, and evening sessions
then you’ll need a “c time”
with the increased complexity of sql that would result
my idea is to create one simple table that covers ~all~ avaliable time slots
[quote=“designtrooper, post:16, topic:207497, full:true”]Where am I going to store other data if there are only two data columns…where am I going to store the days(open,close hours must correspond to the weekdays) and the provider ID
in the key columns
I see…some comments.
B_user_ID refers to the provider…it is a foreign key and it references a table where data about the provider are stored(name etc…)…model_ID refers to a backbone model.
Apart from that though I am not sure if this structure serves our purpose.
Getting the available appointments…by adding the criteria of hours and days the store is open(which this table stores)…if you look in the topic further up we have successfully added the criteria of the already booked appointments.
For example and for provider 3(as seen in your example) his/her available appointments cannot start from 08:00 as the store opens at 09:00:00