[RESOLVED] Reservation Script

[kerell78us]

I am a newbie to php and have am trying to writing a php check availabilty script. From my calendar I am sending two variables to my availabilty.php page, namely: reserve.checkin_date and reserve.checkout_date. I need to have my availabilty.php script query the database rooms and reservation tables and returned the desired results.

I got the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND reservation.reserve_checkin_date, reservation.reserve_checkout_date from RES' at line 1

Below please find details of my php scripts;

<?php

$link = mysql_connect(localhost, root, anything)
or die("I'm sorry this is not available now. ");

if (!mysql_select_db("hotel", $link)) {
echo "I'm sorry this is not available now. ";
exit;
}

$sql = "SELECT rooms.room_no, rooms.room_type_id, rooms.room_name, rate from ROOMS, AND reservation.reserve_checkin_date, reservation.reserve_checkout_date from RESERVATION WHERE rooms.room_id = Reservation.room_id AND Rooms.status = 'V' AND 'reserve_checkin_date'>=' '" . $GET[reserve_checkin_date] . "' AND reserve_checkout_date >=' " . $GET[reserve_checkout_date] ."' GROUP BY rooms.room_no, rooms.room_type_id, rooms.room_name, rate ORDER BY rooms.room_id asc LIMIT 5";

if ( @ ( $sql, $link ) )
{
$query = mysql_query ( $sql );
$row = mysql_fetch_assoc ( $query );

do {
echo "<center><br><br>Results<BR><table border=1 width=90%><tr><th></th><th>Name</th><th>Description</th><th>Maximum<br>Occupants</th><th>Rate</th></tr>\n <tr><td><A HREF='reservation.php?room_ID=" . $row["room_id"] . "'&reserve_checkin_date=" . $GET["reserve_checkin_date"] . "&reserve_checkout_date=" . $GET["reserve_checkout_date"] . ">Reserve</td><td>" . $row["room_name"] . "</A></td><td>" . $row["description"] . "</td><td>" . $row["rate"] . "</td></tr></table></center><br>";

} while ( $row = mysql_fetch_assoc ( $query ) );
}
else {
die ( mysql_error () );
}

?>

Could someone advice where I am going wrong?

Thanks

[bpat1434]

Your SQL is wrong... you put all columns you're going to select after the word SELECT and before the word "FROM", then you have the word "FROM" and the table name(s) you're going to be getting information from, and then the WHERE clause and other clauses.

The proper SQL syntax you're looking for is:

SELECT rooms.room_no, rooms.room_type_id, rooms.room_name, rooms.rate, 
	reservation.reserve_checkin_date, reservation.reserve_checkout_date 
FROM ROOMS, RESERVATION 
WHERE rooms.room_id = Reservation.room_id 
  AND Rooms.status = 'V' 
  AND 'reserve_checkin_date'>=' '" . $_GET[reserve_checkin_date] . "' 
  AND reserve_checkout_date >=' " . $_GET[reserve_checkout_date] ."' 
GROUP BY rooms.room_no, rooms.room_type_id, rooms.room_name, rate 
ORDER BY rooms.room_id asc 
LIMIT 5

[kerell78us]

Thanks alot for your assistance bpat1434.
I tried the revamped query, however, I got the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' GROUP BY rooms.room_no, rooms.room_type_id, rooms.room_name, rooms.rate ORDE' at line 6

Below is the script I used:

SELECT rooms.room_no, rooms.room_type_id, rooms.room_name, rooms.rate,
reservation.reserve_checkin_date, reservation.reserve_checkout_date
FROM ROOMS, RESERVATION
WHERE rooms.room_id = reservation.room_id
AND rooms.status = 'V'
AND 'reservation.reserve_checkin_date'>=' '" . $GET[reserve_checkin_date] . "'
AND reservation.reserve_checkout_date >=' " . $GET[reserve_checkout_date] ."'
GROUP BY rooms.room_no, rooms.room_type_id, rooms.room_name, rooms.rate
ORDER BY rooms.room_id asc
LIMIT 5

[bpat1434]

You don't need the single quotes around "reservation.reserve_checkin_date" that you have. Remove them, also remove the stray single-quote just before the double-quote on that same line.

[kerell78us]

Thanks alot bpat1434 it worked fine.

[bpat1434]

Glad to help. Don't forget to mark this thread as resolved.

Thread Tools --> Mark Thread Resolved.