MySQL query (join)

Question

i have a hotel system with thesw 2 tables

Reservation table

reservation_id pk
Start_Date
End_Date
Customers_Customer_id fk
Rooms_Room_id fk

Rooms table

Room_id pk
type
floor
price

i have this query to retrieve the available rooms

$sql="SELECT Room_id,type,room_floor,price FROM rooms
LEFT JOIN reservation
ON rooms.Room_id = reservation.Rooms_Room_id
where rooms.type='$_POST[type]'
and Start_Date !='$_POST[from]'
and End_Date !='$_POST[to]'"

so the result is fine but it just give me the rooms in the reservation table i want the rooms in the reservation table but not in the searched date plus the others rooms in the rooms table

Not to much of a mysql guy, however would'nt use `$_POST` like that! First Sanitize! read here http://stackoverflow.com/questions/3645131/what-is-a-good-method-to-sanitize-the-whole-post-array-in-php AND here http://stackoverflow.com/questions/4861053/php-sanitize-values-of-a-array/ — Grmn, Apr 23 '13 at 05:06
thinks ya i know the site is lake of security it is just a project for my collage it wont be published online — user2309797, Apr 23 '13 at 05:26

rs. · Answer 1 · 2013-04-23T05:13:26.537

When you add conditions on left outer joined table in where clause it behaves like inner join, this should fix your current query,

$sql="SELECT Room_id,type,room_floor,price FROM rooms
LEFT JOIN reservation
ON rooms.Room_id = reservation.Rooms_Room_id
and Start_Date !='$_POST[from]'
and End_Date !='$_POST[to]'
where rooms.type='$_POST[type]'"

However your will not return correct result, you may want to change your query to this

$sql="SELECT Room_id,type,room_floor,price FROM rooms 
WHERE room_id not in 
(
   --find all rooms that are unavailable
   SELECT Room_id FROM rooms
   INNER JOIN reservation ON rooms.Room_id = reservation.Rooms_Room_id
   WHERE NOT ('$_POST[to]' < start_date or '$_POST[from]' > end_date)
   AND rooms.type='$_POST[type]'
) AND type='$_POST[type]'"

thanks man worked like a charm after changing the place of room type — user2309797, Apr 23 '13 at 05:24

MySQL query (join)

1 Answers1