[RESOLVED] How to establish date status

phpSimon

Hi all

I have a table of items and a table of bookings; each item may have a number of bookings. I want to express the booking status of all items simply in a list; users will click to get detail.

All dates are in YYYY-MM-DD format.

If an item is currently booked, I could use a function like this:

$today = date('Y-m-d');
if ($startDate < $today && $endDate > $today){ // item is currently booked
$status = "Item is booked until ".$endDate;
}

If an item is booked soon, I could do:

if ($startDate > $today){ // item has a booking to come
$status = "Item is booked from ."$startDate;
}

There could be 10 bookings for each item. Validation on the bookings means they don't overlap. However, how do I get the list of bookings from the MySQL database in the most effective way?

I don't want to keep historical bookings, so I think the most logical approach would be to first, delete any bookings whose endDate has passed, then get the list of all bookings ordered by startDate, LIMIT 1. That'll get me the closest booking to today that hasn't finished yet.

I can then check if $today is inside the range of that booking and show 'booked until' or 'booked from' as above.

I would then query the database again to see if there is more than one booking for the item, so I can then also show a total quantity of bookings with a link to see them all.

Is there a better way of doing this?

Many thanks

johanafm

phpSimon;10987763 wrote:
If an item is currently booked, I could use a function like this:
$today = date('Y-m-d');
if ($startDate < $today && $endDate > $today){ // item is currently booked
$status = "Item is booked until ".$endDate;
}
If an item is booked soon, I could do:
if ($startDate > $today){ // item has a booking to come
$status = "Item is booked from ."$startDate;
}
There could be 10 bookings for each item. Validation on the bookings means they don't overlap. However, how do I get the list of bookings from the MySQL database in the most effective way?

You use the exact same logic as you would in php. That is, for the currently booked

SELECT i.name, b.start, b.end
FROM item i
INNER JOIN booking b ON i.id = b.item_id
WHERE NOW() BETWEEN start AND end

The query for future bookings is similar, but having WHERE start > NOW().

phpSimon;10987763 wrote:
I don't want to keep historical bookings

Why not? In my experience, you always come to a point where you want to check historical data. What items are the most popular? Do we have anything which has never been booked? Assuming item X has to be serviced after roughly 500 uses, what X items are likely to need service in the near future? What items are likely to need being replaced etc.

phpSimon;10987763 wrote:
, so I think the most logical approach would be to first, delete any bookings whose endDate has passed, then get the list of all bookings ordered by startDate, LIMIT 1. That'll get me the closest booking to today that hasn't finished yet.

Which you could still just as easily do with

WHERE end > NOW()

and then whatever other logic is required, since end <= NOW() means that item is no longer booked, and thus the query will NOT select any such items.

Also, if you index start and end dates, queries such as these should not be a problem even on your own laptop as long as you have no more than a few million bookings in the table.

phpSimon;10987763 wrote:
I can then check if $today is inside the range of that booking and show 'booked until' or 'booked from' as above.

No need to check that. Let the database do these comparisons. Once your query returns data, you are certain that $today is inside the date range. Or not inside it. It depends on the query you execute.

phpSimon;10987763 wrote:
I would then query the database again

You never need to perform a second query when you are after data such as this
1. I want to inspect item X matching these things
2. And then I want to check if Item X also...

phpSimon;10987763 wrote:
to see if there is more than one booking for the item, so I can then also show a total quantity of bookings with a link to see them all.
[/qutoe]
Assuming you are looking at a particular item, let's say one having id=10
SELECT i.id, i.name, b.start, b.end
INNER JOIN booking b ON i.id = b.item_id AND b.end > NOW()
WHERE i.id = 10
ORDER BY b.start ASC
You will instantly know that:
1. If no rows are returned, this item has no future bookings
2. Due to the "order by start asc", you know that the first row is either a current booking, or the next upcoming one
3. The total number of rows returned are the total number of present + future bookings for this item.

phpSimon

Thank you johanafm for such detailed feedback, which is really helpful.

I'll try a few things out and post back how it worked.

Thanks again

phpSimon

There is so much good advice in your post that it's tricky to explain exactly what parts I used - but thanks again for all the suggestions. I also took on board your 'deletion of bookings' comment and am no longer doing that!

I have a different question about the best way to report on bookings, but I think it needs a new thread so I'll set this one resolved.