Renting table/schedule

[Amplivyn]

I have a few questions regarding a table which displays rent slots. It will basically have the dates on the leftmost column (Jan 1,2,3,...) and the possible renting hours on the header (12:00-15:00, 15:00-18:00, 18:00-21:00 and so on). On the website, it will display 30 days from today, i.e 30 rows (Jan 29 to Feb 27 for instance). A picture of what I mean may help (not 30 days but never mind).

 

 

Is it possible to automatically add rows to the MySQL table including free slots or would it be better to already have slots planned and ready in the existing table (say, an entire year of dates for example)?

 

And just wondering what you guys think, would it be a good idea to have "free" and "booked" stored as Boolean values (0s and 1s) and print what they mean as the page processes?

 

Also, to display today's date as the first row, would it be better to delete days that have already ended and fetch the first row OR search for today's date in the table and then display it?

 

Finally, the users/customers will be able to choose slots only by contacting the website admin who then changes free slots to booked slots. Is there an easy way to modify these slots from the browser? Ideally, the admin would highlight the slots to be booked and the code will handle the rest, but I'm not really sure how to do this. The admin may be changing many slots at a time (often consecutive but not always). Does anyone have any ideas on how to traverse through a table?

[Philip]

Instead of storing all of the possible values, store only the booked ones. Then create a loop that goes through all of the possible time slots and checks against if there is an entry for that slot or not.

[Amplivyn]

That sounds like a good idea, I'm going to try to implement it now. I'm still stuck on how to let the admin store those booked values, I was thinking perhaps have them enter some sort of coordinates to determine the size of the booked block, for example, 1-8 width and 4-7 height for instance to cover the first block in the picture above (all hours from the 26th to the 29th of Jan). Of course I would have to link dates so that I don't get a fixed block but I'll figure it out when I get to it, thanks!

[Barand]

Easiest way is to display checkboxes when an administrator is viewing the chart so one or more can be selected for a booking. You mention linking dates. I'd create a bookings tables and a timeslot table so a booking can have one ore more slots.

 

Booking : bookingID, booked_by, booking_date

Timeslot : slotID, bookingID, time_from, time_to, slot_date

[PFMaBiSmAd]

As to deleting past information vs querying for information from the display start date through the next 30 days, you should keep past information for historical reasons. In real applications, data is rarely deleted.

[Amplivyn]

Alright here's what I have so far. I have 3 tables on the server, a customer table (name, contact details, etc.), a transactions table (user id, amount paid, date, etc.) and a bookings table (user id, date, slot). Since there are only 8 fixed slots per day (every 3 hours), instead of a starting time and ending time, I will only have a slot number (1-8 for instance). On the actual page, there will be some code to get today's date (DateTime object) and output a row of "FREE" slots, add a day and output more slots until the desired number of rows is reached. However, it will also get the bookings table (sorted in ascending order by date) and while outputting those rows, it will check to see if there are any bookings on that day and if so, output the appropriate "BOOKED" slots.

 

For the actual booking, it may be tedious to check boxes, especially if some are ahead of time and not displaying on the table. Instead, I will have a form requesting a starting date and either an ending date or a number of days. That way if it starts on 1st March, the admin could choose the last day as 3rd March or 3 days of bookings. Of course, I will also have check boxes to choose the slots on those days. I'm also thinking of adding an "Except" selection list, for example, except weekends, Mondays, etc.

 

For anyone interested about the 3 tables above, the admin will choose an existing customer or make a new one (enter details and so on). The admin will then proceed to record a transaction (if any) and in the case of renting stuff, he will then choose the required bookings and store them. As PFMaBiSmAd mentioned, these records are unlikely to be deleted and will be kept for future purposes.

 

Finally, I wanted to see what you guys think is better, the website will have 3 of these rental tables for 3 different products. Would it be better to have 3 different booking tables on the server or just one with a "product type" field to filter out results? I'm leaning more towards the latter but I would like to hear your opinions, thanks!

[Amplivyn]

Alright, I might need some help with the last step, I am at the stage where I want to output the booked slots. I currently have some code to output the required table and fill it with free slots. I need to alter it to output booked slots if the current row (date) is inside a booked date. The dates in the MySQL database are stored as starting dates and ending dates in "YYYY-MM-DD" format. Using a query, I am able to get these starting/ending dates in pairs, still in MySQL format.

 

While outputting the table, I am using the DateTime object to keep track of days. This makes it easy to check if a date is before or after (or between) other DateTime objects (using < and >).

 

My problem is, is there an easy way that I am unaware of to convert MySQL dates to DateTime objects? Also, the algorithm I have in mind is something like this, just before outputting the row and check if that date is in any of the starting/ending periods (linear search), if yes, output the appropriate booked slots. This is done for each row, it's all I have, but it seems really inefficient.

[Barand]

$mysqlDate = '2013-02-01';

$dateObj = new datetime($mysqlDate);

 

Select just the rows you want to output using a WHERE clause in your query

Edited February 8, 2013 by Barand