I'm making a calendar using MySQL, html, JS and PHP.
This calendar is for reserving the ballroom here in the building.
There may be two reservations the same day, morning and afternoon that would be "00:00 to 11:59" and "12:00 to 23:59".
My question:
I would like a suggestion for the best way to do this, I have already thought about doing with two fields date and the other time .
I also wondered if you can use datatime , timestamp .
Any tips or suggestions?
I would store with 2 fields.
data , field date same (or int counting number of days from a default date).
periodo field int of 1 byte only, or enum being:
1 = morning
2 = late
3 = both periods
This simplifies the problem.
To test date availability, just do this select :
SELECT periodo WHERE data=$data AND ( periodo=$periodo OR periodo=3 OR $periodo=3 );
periodo=$periodo means conflict, if you asked for morning and have morning, or if you asked for late and you already have afternoon
periodo=3 means conflict, because if you already have a busy day, you can not schedule anything else on that date.
$periodo=3 means conflict, because if you are trying to schedule all day, any existing scheduling prevents scheduling.
If the query does not return any records, you can do a new schedule on date $data for period $periodo .
If, on the other hand, you return records, you can not get the new schedule, as it will have a conflict.
The best way would be to use datatime
To get dates and times separately, use:
$datetime = '2010-07-05 12:11:00';
list( $date, $time ) = explode( ' ', $datetime );
echo 'Data: '.$date;
echo 'Hora: '.$time;
Make a Boolean field (in MySQL: bit (1)) where 0 indicates the morning reservation and 1 indicates the afternoon reservation.
Regarding the date: If for ease, use the DATE type, it stores dates in the following format: 'YYYY-MM-DD'
If it's space saving, create a SMALLINT field and store the number of days between the event and a date that you set, for example, between 01/01/2015 and the day of the event and leave the javascript do the math to turn this into a beautiful date.
You can also store everything in the DATETIME type, but I find it unnecessary.