MySQL/PHP/ and some Flash help

[matthewod]

I have an issue with a Flash file that was put together by someone other than me. I am having to try to figure out how this works in order to fix it. Here is the situation: I have to find out if there is an opening between two dates, like a schedule and out put that information into a Flash file. This is basically for a reservation system. So I have the start time and the end time and I have to see what "rooms" are available for that time period and length of time.

Here is what I have:

<?

$selected_start = strtotime($_SESSION['start_date']);
$selected_end = strtotime($_SESSION['end_date']);


$sql = 'SELECT lot_id,start_date,end_date, reserve_id, confirm_id FROM table_name' ;
$result = mysql_query ($sql, $connection) or die(mysql_error());

$reserved_array = array();

$reserved_array2 = array();

/* Get all lot_ids in the table. */
$sql = 'SELECT lot_id,start_date,end_date, reserve_id, confirm_id FROM [table_name]' ;

$result5 = mysql_query ($sql, $connection) or die(mysql_error());


while ($row = mysql_fetch_array($result5)) {
	array_push($reserved_array2, $row['lot_id']);

}

/* Get lot_ids that are after any any reserved lots end date. */
while ($row = mysql_fetch_array($result)) {
	$reserved_start = strtotime($row['start_date']);
	$reserved_end = strtotime($row['end_date']);


	if ($reserved_end < $selected_start) {
		array_push($reserved_array, $row['lot_id']);
	}
}


$sql = 'SELECT lot_id,start_date,end_date, reserve_id, confirm_id FROM [table_name] ' ;
$result3 = mysql_query ($sql, $connection) or die(mysql_error());


/* Get lot_ids before reserved lots start date and end date. */
while ($row = mysql_fetch_array($result3)) {
	$reserved_start = strtotime($row['start_date']);
	$reserved_end = strtotime($row['end_date']);

	if ( $reserved_start > $selected_start && $reserved_start > $selected_end ) {
		array_push($reserved_array, $row['lot_id']);
	}
}


echo('<?xml version="1.0" encoding="iso-8859-1"?>'."\n");
echo('<lots>'."\n");

$sql2 = 'SELECT * FROM lot_info order by lot_id';
$result2 = mysql_query ($sql2, $connection) or die(mysql_error());

while($row = mysql_fetch_array($result2))

{
	if (in_array($row['lot_id'],$reserved_array)) {
		echo('<lot id="'.trim($row['lot_id']).'" type="'.trim($row['type']).'" />'."\n");
	}
}


$sql = 'SELECT * FROM lot_info order by table_2';
$result4 = mysql_query ($sql2, $connection) or die(mysql_error());

/* get any lot_ids that are NOT in the table_name table. */
while($row = mysql_fetch_array($result4))

{
	if (!in_array($row['lot_id'],$reserved_array2)) {
		echo('<lot id="'.trim($row['lot_id']).'" type="'.trim($row['type']).'" />'."\n");
	}
}


echo('</lots>');

?>

Basically what is happening with the above code is if the lot_id is listed more than once in the [table_name] then this causes a conflict and will not show the "room" blocked out, when in fact it should be blocked out.

If my start date is 2006-01-01 and my end date is 2006-02-01 and my "room" number is 5001 and the next reservation for that "room" is 2006-04-01 thru 2006-05-012, the "room" should be available from 2006-02-01 thru 2006-02-28. But it is not showing up as being available.

thanks in advance for any advice.

Matt D