How do I set future dates?

[darkside619]

Hello.  I wanted to make a table where the dates are listed going forward at least one year.  So, at least 365 records, but each date should be unique and should start from today until one year from now.  I tried to make a date column and make it a primary key.  

 

However, an error comes up when I try to insert more than one record.  This is for a calendar that I'm building so I need the dates listed one year in advance and it would be great if new dates could be added without my having to manually go in there and insert new records to stick to my 1 year in advance rule.  

 

For that aspect I might have to create a php script where the date records are added every time a user logs in, to make sure they are up to date, but anyways what I don't understand is why I can't make a table with 1 year of date records in advance, where each row has a unique date starting from today.

[CroNiX]

I don't understand why you'd need to do that and it sounds very inefficient.

 

Personally I'd only store entries for days that have some sort of "event". Then build the calendar for a given month and query the db for all entries in that month. When building the calendar day by day, check to see if an event exists for that "day" from your result set and if so display it.

[CroNiX]

It might help more to explain what you want the end result to be.

[darkside619]

I don't understand why you'd need to do that and it sounds very inefficient.

 

Personally I'd only store entries for days that have some sort of "event". Then build the calendar for a given month and query the db for all entries in that month. When building the calendar day by day, check to see if an event exists for that "day" from your result set and if so display it.

 

It might help more to explain what you want the end result to be.

 

Well I'm new to making calendars so I figure I don't have it that well planned out but here is what I was thinking.  Keep in mind that the intention is for this to be used for booking appointments with personal trainers.

 

My plan is to create a table with 96 time slots of 15 minutes each, since appointments can vary in length and be either 15 minutes, 30 minutes, 45 minutes, 1 hour, 2:30 minutes, etc. long.  So, what I would do is fill in the dates for a year in advance and set each time slot to 0 or 1 depending on the schedule of the personal trainer.  A 0 means that he is not available in that 15 minute time slot whereas a 1 would mean that he is.  So, lets say that a user chooses a training session that is 1 hour long.  Then, I would query the database to see where 4 slots are available in succession that equal to 1.  

 

So, lets say for instance that from 2pm-3pm the trainer is available. This would mean that 2:00 has a value of 1, 2:15 has a value of 1, 2:30 has a value of 1, and 2:45 has a value of 1.  So, my php script would query the database and add all the time slots in a loop to see where four #1 slots are next to each other. 

 

I realize that this would make my application query the database a hell of a lot since each day of the calendar would have to be searched like this.  I'm open to ideas though.

Edited August 18, 2014 by darkside619

[darkside619]

I should also point out that the calendar application that I'm using reads events from a json file.  If a day is available for booking a personal trainer then that day has a dot on it, indicating that there are time slots available. This is why I thought that I'd populate the json file from a mysql table rather than hard code it myself. 

Edited August 19, 2014 by darkside619

[Ch0cu3r]

@darkside619

Hasn't Barand already suggested to you what you need to do in your other post here. Barands select query returns all the available hours a trainer is available for taking into account their scheduled appointments. Sounds to me you are not understanding Barands solution?

[Zane]

Any SQL advice Barand gives you, soak it up like a sponge.  He (and fenway) have helped me several times over the years, without any disappointment.

 

Creating a blank table with premade records is the most inefficient way possible to do what you are doing.  One day can have several appointments from what I gather so what good would it do you to prepopulate a table?

 

 

 

I tried to make a date column and make it a primary key.

Again, you will have multiple appointments for a given day, so using the DATE as a primary key will not work like a primary key should.  Most primary keys are set to auto-increment, so what is going to happen when you want to add a new booking appointment?  You'll have to insert the date everytime and not only that, you would need to check if there is s a duplicate key, since you're not automatically incrementing your index.

 

What Barand has shown you in the other thread is more than enough to get started.  Read over it several times if you have to.

I'm closing this thread so you can continue with your original one.

 

http://forums.phpfreaks.com/topic/290480-appointment-booking-script-for-calendar/?do=findComment&comment=1487931