Msql Php Booking

[S34N]

Please some1 help me with this code.

 

i've tried soo many times to retrieve the rooms that are not booked. if no rooms have been booked for a specified period match with the user input, then the room is available for booking. and now my PDO Mysql code is spinning in my head...

 

 

 

<?php

/**
* @author S34N
* @copyright 2012
*/

class Connection
{	
Public function dbConnect()
{
	return new PDO("mysql:host=localhost; dbname=waterfall", "root", "6354");
}
}

class Date_a
{
private $db;

public function __construct()
{
	$this->db = new Connection();
	$this->db = $this->db->dbConnect();
}

public function Query($arr, $dep)
{
	try
	{
		if (!empty($arr) && !empty($dep))																							   //Check_In between booked dates	 Check_out							   User Prompted arrival date			   User Departure Date
		{
			$st = $this->db->prepare("SELECT `schedule`.`Check_In` , `schedule`.`RoomNo` , `schedule`.`Check_Out` FROM schedule WHERE (Check_In BETWEEN ? AND ? || Check_Out BETWEEN ? AND ?)");
			$st->bindParam(1, $arr);
			$st->bindParam(2, $dep);
			$st->bindParam(3, $arr);		//("SELECT `schedule`.`Check_In` , `schedule`.`RoomNo` , `schedule`.`Check_Out` FROM schedule WHERE ((Check_In >= ?) AND (Check_Out <= ?))");
			$st->bindParam(4, $dep);		//("SELECT `schedule`.`Check_In` , `schedule`.`RoomNo` , `schedule`.`Check_Out` FROM schedule WHERE ((? NOT BETWEEN Check_In AND Check_Out) AND (? NOT BETWEEN Check_In AND Check_Out))");		
			$st->execute();				 //("SELECT `schedule`.`Check_In` , `schedule`.`RoomNo` , `schedule`.`Check_Out` FROM schedule WHERE ((Check_In BETWEEN ? AND ?) || (Check_Out BETWEEN ? AND ?) || (? BETWEEN Check_In AND Check_Out) || (? BETWEEN Check_In AND Check_Out))");

			if($st->rowCount() >= 1)	
			{
				$num = $st->rowCount();
				echo $num;
				echo "Booked !";
			}
			else
			{
				$status = "Available";
				$num = 0;
				echo $status;
			}
		}
		else
		{
			echo "Empty input.";
		}
		$this->db = null;   //Close Database Connection
	}
	catch (PDOException $e)
	{
		echo $e->getMessage(); //Returns error message.
	}
}
}
?>

Edited October 21, 2012 by fenway

[Barand]

the attached diagram will give you the search conditions for booked rooms