[RESOLVED] Searching through dates (date range)

bastienvans

Hi,

I am working on a property rental website. I have a form with 2 date fields, check in and check out, both posted to check.php to check for availability.

I have a table named 'Calendar' with 2 columns, propertyID, and date. If a property is booked, there is a row in my calendar table, like so:

propertyID | Date

1234 | 2010-03-21

If the property is available in the selected range, no rows are added in the table.

Here comes my problem...

If a property is booked, it displays:

8731 is Booked. Date: 2010-03-26

If a property is available, it displays:

8731 is Booked. Date: 2010-03-26
4564 is Available. Date: 2010-04-20
4564 is Available. Date: 2010-04-21
8731 is Available. Date: 2010-03-26
8731 is Available. Date: 2010-03-29

I need to exclude the rows that are booked from the rows that are available, I have been toying around with this for a while with no luck.

This is my code so far:

$checkin = $_POST['checkin'];
$checkout = $_POST['checkout'];


//FORMATS THE DATE

$checkinvar = explode('/',$checkin);

$start = $checkinvar[2].'-'.$checkinvar[0].'-'.$checkinvar[1];

$checkoutvar = explode('/',$checkout);

$end = $checkoutvar[2].'-'.$checkoutvar[0].'-'.$checkoutvar[1];




$query = "SELECT * FROM calendar";
$result = mysql_query($query) or die(mysql_error());


$query_a = "SELECT * FROM calendar WHERE date BETWEEN '$start' AND '$end'";
$result_a = mysql_query($query_a) or die(mysql_error());
$returned_rows = mysql_num_rows($result_a);

if($returned_rows != 0) 	
	{
				while ($row = mysql_fetch_array($result_a))
					{			
						echo $row['propertyID'].' is Booked. Date: '.$row['date'].'<br />';
					}

} 

			while ($var = mysql_fetch_array($result))
				{			
					echo $var['propertyID'].' is Available. Date: '.$var['date'].'<br />';
				}

Any help will be greatly appreciated.

Pikachu2000

If I understand correctly, you want it to work like a hotel reservation system, where if any date within the date range entered isn't available, the entire range is considered unavailable. Is that correct, or am I misunderstanding your intentions?

If it's the way I think it is, you might be going about this the wrong way, well, sort of. Instead of running the query to select the dates that are submitted in the form, it may be better/easier to first run a COUNT() query to see if any of the dates in the range are already listed as booked. Any result > 0 would indicate the property is not available for the date range searched for, and no further query would be necessary, just a message stating "not available for those dates" or whatever.

bastienvans

thank you for your input, sir. i will try it your way.

Pikachu2000

I may have been editing my post while you were reading it. I added the first paragraph . . .

bastienvans

oops, didn't see that. Yes, I am indeed working on something similar to a hotel booking system. I've been working on this for the past 2 days with no luck... I always get close to getting it working, until something goes wrong.

I am going to mess with SELECT COUNT for a while.

Thanks again for your reply.

bastienvans

I am trying to understand how to make it work with your suggestion, but I am not getting anywhere.

I don't care for it so much to print if a property is booked. I just need to print the properties available. This is driving me nuts, lol.

Pikachu2000

So, you want to print the available dates of all properties within a range of dates, even if a date within that range is not available?

For example if the range searched for is 4/1 - 4/4, and a property is available 4/1, 4/3 and 4/4 but not 4/2, should it still be included in the results?

bastienvans

Pikachu2000;10946943 wrote:
So, you want to print the available dates of all properties within a range of dates, even if a date within that range is not available?

For example if the range searched for is 4/1 - 4/4, and a property is available 4/1, 4/3 and 4/4 but not 4/2, should it still be included in the results?

No, it should not be included in the results.

For instance, given property ID 1234 is booked from 2010-04-01 till 2010-04-09... if the user searches for 2010-03-29 to 2010-04-04, it will exclude the property from the results, but display all the other properties available.

bastienvans

This is what I came up with:


$query = "SELECT COUNT(*) FROM calendar WHERE date BETWEEN '$start' AND '$end'";
$result = mysql_query($query) or die(mysql_error());

$count = mysql_result($result,0);

if($count > 0) 

{
				$query = "SELECT * FROM calendar WHERE date BETWEEN '$start' AND '$end'";
				$result = mysql_query($query) or die(mysql_error());
				while ($row = mysql_fetch_array($result))
					{			
						echo $row['propertyID'].' is Booked. Date: '.$row['date'].'<br />';
					}
}

else

{
	echo 'print all'; // EXCEPT unavailable properties 
}

bastienvans

BUMP

anyone else? I am still very confused. thanks!

Pikachu2000

Sorry I haven't had a chance to get into this further. I have to get some work stuff done. I see it's marked resolved, though. Did you get it figured out?

bastienvans

No problems, thanks for your help! This is what I ended up with. I still need to test it further, but so far it works.

$query1 = "SELECT DISTINCT propertyID FROM listings";
$result1 = mysql_query($query1) or die(mysql_error()); 
	while ($row = mysql_fetch_array($result1))
		{
			$propertyID = array($row['propertyID']);
				foreach($propertyID as $ID)
					{ 
						$query = "SELECT * FROM calendar WHERE date BETWEEN '$start' AND '$end' AND propertyID=$ID";
						$result = mysql_query($query) or die(mysql_error()); 
						$numrows = mysql_num_rows($result);
							if($numrows == 0)
								{
									echo 'print '.$ID.'<br />';
								}
					}
		}

Pikachu2000

Oh, good. Didn't mean to leave you hanging . . .