[SOLVED] Difficult (for me) Query - JOINs

[PHP Monkeh]

Hi all, been a while since I've visited these forums but I'm stumped!

 

I'm building a room booking system, I won't go in to all the details but all that you need to know for this query is I have three tables:

 

bookingSlots
---------------
id
position


rooms
---------------
id


bookings
----------------
id
roomId
slotId
bookingDate

 

 

bookingSlots represents time slots, and when a user clicks on one of these slots I want to be able to display all available slots for that room and date from that slot onwards (this is where the position column comes in).

 

I've tried a few queries and must admit I'm getting nowhere.  I imagine it would be something like this:

 

SELECT bookingSlots.id FROM bookingSlots
(SOME SORT OF JOIN IN THE MIDDLE HERE WITH A COUNT(*) TO SEE WHETHER THE SLOT HAS ALREADY BEEN BOOKED)
WHERE bookingSlots.position > $currentPosition AND slotIsBooked = 0

 

As I'm useless with JOINs any help would be great   In the meantime I'll keep trying.

 

Thanks

[PHP Monkeh]

I've managed to accomplish what I wanted, but it wasn't using a single query like I was hoping (so it probably isn't as efficient as it could be).  This is what I did, but if anyone can shrink this to a single query it would be great:

 

45     public function getAvailableSlots($roomId, $slotId, $date) {
46         $startSlotResult = $this->db->query("SELECT `position` FROM `bookingSlots` WHERE id = '" . $this->db->escape($slotId) . "'");
47         $startSlotArray = $startSlotResult->fetch_array(MYSQLI_NUM);
48 
49         $slotResult = $this->db->query("SELECT `id` FROM `bookingSlots` WHERE `position` >= '" . $startSlotArray[0] . "'");
50         while($slots = $slotResult->fetch_array(MYSQLI_ASSOC)) {
51             if($this->checkSlot($roomId, $slots['id'], $date) == 0) {
52                 $availableSlots[] = $slots['id'];
53             } else {
54                 break;
55             }
56         }
57         return $availableSlots;
58     }

 

I won't post the checkSlot() function but all it does is runs a query and returns a 0 if the slot isn't booked.

[xylex]

I'm not sure if I quite understand what these columns are all doing, but if I'm following correctly, you want to return bookingSlots where there isn't a match with that slot id and room in bookings, basically a negative join?

 

For stuff like that, I usually do a left join on the table and limit the results to where the results from that table is null.

 

So in your case, maybe something like

$sql = "
SELECT bs.id from bookingSlots bs 
  LEFT JOIN bookings b ON (bs.id = b.slotId AND b.roomID='$roomId')
WHERE b.id IS NULL"