Need a guru to crack this: Dates & Availability

derekcpt

Hi.

I really need someone to crck this for me. This is keeping me busy for ages now and I'm really getting furstrated and fed with myself.

I need a simple solution:

I have a database with rooms. I want to have the rooms booked. So, this is fine. Now I need to do an availability check according to my date selection. So after posting the cjeckin date and the checkout date, the result page has to show the pages available.

I am going to include the crap that I have done, or rather, just the main points, but please, if you have a solution to crack this once for me, you'll save my life.

So, I've used the mysql selection like this:

$PostCheckIn = $_POST['CheckIn'];
$PostCheckOut = $_POST['CheckOut'];

mysql_select_db($database_join, $join);
$query_selection = "select * from rooms LEFT JOIN booked on rooms.RoomName = Booked.RoomName";

This is my sample sql tabase - I don't know if this is correct. I can do the coding to add a booking and display it etc etc. but I'm simply to dumb to achieve what I'd like to achieve. So, by saying this, I'm sure that my tables are quite dumb too, ;-) I mean, if I don;t know in which format and how to retrieve the booking availability, I won't know how to set-up the database. So please please crack this for me.

Ok, here is my mysql database

DROP TABLE IF EXISTS `booked`;
CREATE TABLE IF NOT EXISTS `booked` (
  `bookID` int(11) NOT NULL auto_increment,
  `RoomName` text,
  `CheckIn` date NOT NULL default '0000-00-00',
  `CheckOut` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`bookID`)
);

DROP TABLE IF EXISTS `rooms`;
CREATE TABLE IF NOT EXISTS `rooms` (
  `roomID` int(11) NOT NULL auto_increment,
  `RoomName` text,
  `Desc` text,
  `Rate` text,
  PRIMARY KEY  (`roomID`)
);

:eek::mad::mad:

efficacious

what errors are you getting... you haven't stated the problem

illzz

Using the following query on your current DB structure will return only the available rooms for the given data range. I tested this only on a small set of data, so you may want to test further with more data.

$query = <<< EOT
 SELECT * FROM rooms r
  LEFT JOIN booked b
   ON r.RoomName=b.RoomName
 WHERE ("$PostCheckIn" >= b.CheckOut)
 OR ("$PostCheckOut" <= b.CheckIn)
 OR (b.RoomName IS NULL)
 GROUP BY r.roomID
EOT;

Here is a little description of my WHERE syntax:

 *   WHERE (We are checking in after the room is booked)
 *    OR (We are checking out before the room is booked)
 *    OR (The room is not in the booked table)

Some notes:
1) You may want to use 'roomID' in the 'booked' table rather than 'RoomName', leading into....
2) create a foreign key mapping between the 2 tables, using the indexed roomID
3) You may want to update you Column Types. e.g. Making your ID fields UNSIGNED and changing Rate to a numeric type (instead of Text)

derekcpt

First of all, thank you so much, your post seems to be very logic and working.In some other way, I got to put together (after cracking my brains to shatters) the following. Its is quite similar to yours, but yours seems to be more practical.

if you check out the coding below, you will see (I guess so) that PHP will regard the input as just numbers or values but not as dates? I'd like to search for the dates specified by my field post. And the way it is right now, well I think this is not the way to do it.

Guess I have to convert the dates? No clue.

	$start = "2008-08-29";
	$end = "2008-09-10";


// lists objects which are booked within the specified time range
$sql  = "SELECT DISTINCT rs_data_objects.room_id ";
$sql .= "FROM rs_data_bookings INNER JOIN rs_data_objects ON rs_data_bookings.room_id = rs_data_objects.room_id ";
$sql .= "WHERE ((rs_data_bookings.book_end > '" . $start . "' ";
$sql .= "AND rs_data_bookings.book_end <= '" . $end . "') ";
$sql .= "OR (rs_data_bookings.book_start < '" . $end . "' ";
$sql .= "AND rs_data_bookings.book_start >= '" . $start . "') ";
$sql .= "OR (rs_data_bookings.book_start <= '" . $start . "' ";
$sql .= "AND rs_data_bookings.book_end >= '" . $end . "')) ";

derekcpt

Guess I have to convert the dates? No clue.

	$start = "2008-08-29";
	$end = "2008-09-10";


// lists objects which are booked within the specified time range
$sql  = "SELECT DISTINCT rs_data_objects.room_id ";
$sql .= "FROM rs_data_bookings INNER JOIN rs_data_objects ON rs_data_bookings.room_id = rs_data_objects.room_id ";
$sql .= "WHERE ((rs_data_bookings.book_end > '" . $start . "' ";
$sql .= "AND rs_data_bookings.book_end <= '" . $end . "') ";
$sql .= "OR (rs_data_bookings.book_start < '" . $end . "' ";
$sql .= "AND rs_data_bookings.book_start >= '" . $start . "') ";
$sql .= "OR (rs_data_bookings.book_start <= '" . $start . "' ";
$sql .= "AND rs_data_bookings.book_end >= '" . $end . "')) ";

illzz

PHP isn't very strict on datatypes (as some other languages are). In this case it will see the input as strings and pass it on when building the query string, so no need to change anything here. You are more likely to run into datatype problems in MySQL if the inputs are not formatted in a recognizable date format.

Your logic seems to be fine, from what I can gather. If you alter part of your structure, either the MySQL datatypes or the PHP code prior to the query, there may be ways to optimize the query. You may gain a little bit of performance when running the query, but it would result in a slight performance hit in PHP and/or increased size of the database.

For something small like this, you probably wouldn't even be able to notice any difference. So as long as you are getting the data that you need, you are probably good with what you have now.

derekcpt

Hey,

I'd just like to thank you for giving me a piece of mind, this worried me a lot.

Thanx again.

Currently I am still working on the script and it is cracking my brains.:eek:

Nonetheless, wish I had the knowledge of you guys.,

Thanx again.,

illzz

No problem... don't forget to mark this thread as resolved in the "Thread Tools".

On a side note, be careful with how you are creating your query string (the leading and trailing spaces). I used to use a similar style, using spaces at the end (or beginning) and would sometimes forget a space somewhere, resulting in some words joining, causing MySQL syntax errors.

That's when I started using Heredoc Syntax a lot more. It helps make things more legible and you can also copy queries directly from the code easier (not having to replace all the quotes, semicolons, etc...).

This is just my preference, but I thought I would share.