Best practice (IMHO) is to use the built-in MySQL DATE data types [dev.mysql.com] in almost all cases, especially when you want to perform any sort of date manipulation or calculations. So by attempting to "make it simpler", you've made more work for yourself. Take a look at the MySQL DATE and TIME functions [dev.mysql.com].

I also don't quite see the logic in checking as to whether the room is available or not when you have a `codate` column. If the `codate` exists, then surely the room isn't available, correct? Or am I missing something?

Hello,

Thanks Ill look into the MySQL Date option however since a lot of my code already revolves around date I would like to try to find a way to do it with this.

As for the problem with codate the problem here are some possibilities:

- Someone could reserve within the chosen dates, this would mean the room is no longer available for that date range. (Example: I want to find rooms available from Nov 20 - Nov 30. Someone within that time is already reserved for room 4 from Nov 22-Nov 23 - I need to be able to check for something like this)
- Someone could have reserved a room to check in BEFORE the checkin date chosen and check out either BEFORE or AFTER the checkout date, in which case again that room wouldnt be available.
- Someone could have reserved a room that has a check in date AFTER the checkin date selected but at the same time before the checkout date chosen and be reserved to checkout after the checkout date chosen. (this one is kind of hard to explain but for example: I want to look for rooms available between Nov 20-24 but room #5 is reserved from Nov 21 to 24 or Nov 23 - 30 - both cases room #5 not available)

Note: In my above options when I say checkin and checkout dates I'm talking about the range of dates chosen by the user using the form, not the actual reservation dates in the records.

Thanks again.

I agree w/ Blang that your date values should be stored in the DB in native DATE format. This makes querying the DB much simpler. You only need to convert to string when you want to display the dates like in the reservation UI.

I also think your approach of first querying to get a list or rooms (one round trip) and then requerying for each room to see if it has a vacancy (an additional roundtrip for each room in list) is very inefficient and doesn't scale.

You're likely better off issuing a single query (or stored proc call) using checkin/checkout date (and any other modifiers the consumer requested like types of bed, smoking vs. non-smoking, etc.) and letting the DB server do all the work and return your results in one round trip like a recordset of all rooms that are available that match the query parameters. That is what a DB is for... not just a data storage device. You may as well be using flat files if that is how you query avoiding the use of JOINs.

You only described what appears to be the reservation table. I'm assuming there is also a "rooms" table that lists all of your rooms, possibly the room type and other attributes of the room. If this is the case then you should be able to get back a list of ALL rooms that are available for a given date by LEFT OUTER JOINing the rooms and reservations tables. In otherwords get the list of rooms from the rooms table that do NOT have a row in the reservations table for ANY of the dates in question.

How easy or how hard this is for you is going to depend on how you setup your DB schema. My guess is that your problem lies in your schema, not in this particular query. Your schema is likely not sophisticated or normalized enough to support the kind of queries you need to be performing.

You can likely make this work by running through a series of LOTS of queries and LOTS of roundtrips to the DB to finally arrive at an answer. But this is a poor approach. IMO since you are obviously just developing this, you should be working on FIRST building a schema that lends itself to easily getting to answers to all of your queries, those you already know about AND ones you haven't thought about yet but will eventually. This means normalizing your data.

Just looking at your DB table layout for the one table you described, I would be willing to bet that even if you get this problem solved, that you'll hit many more queries that you are going to need to make that are going to be equally as difficult... and shouldn't be... unless you back up and re-evaluate your schema.