Listing Available Rooms Query Help Needed

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Timmymna
Forum Newbie
Posts: 5
Joined: Sun Feb 13, 2005 7:24 am
Location: UK

Listing Available Rooms Query Help Needed

Post by Timmymna »

I am making a hotel booking webpage and need some help.

Here are the tables involved.

What I want to do is to be able to search for rooms which are available for booking but not sure how to go about getting a list of the rooms without the ones that are booked at that time.

I have a query that will find ones that are booked but I'm not sure if I can use this but invert the results some how to get the other rooms or if I need a completly different approach.

Code: Select all

SELECT booking.bookedfrom, booking.bookedto, room.number, room.type FROM booking, room, roomsbooked 

WHERE
((booking.BookedTo > $arrival) AND (booking.BookedTo < $departure)
OR (booking.BookedFrom > $arrival) AND (booking.BookedFrom < $departure)
OR (booking.BookedFrom < $arrival) AND (booking.BookedTo > $arrival)
OR (booking.BookedFrom = $arrival)
OR (booking.BookedTo = $departure)) 

AND booking.bookingid = roomsbooked.bookingid 
AND roomsbooked.roomid = room.roomid 
AND room.type = $type
Thanks for any help or ideas :D
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

The tables ::

Code: Select all

Table booking
bookingid	customerid	bookedfrom	bookedto
14	       53	        2007-01-01	2007-02-02
15	       54	        2006-01-01	2006-02-02
16	       52	        2004-01-01	2004-02-02
17	       51	        2005-01-01	2005-02-02

Table roomsbooked
bookingid	roomid
14	       2
15	       5
16	       8
16	       7
17	       6
17	       1
17	       9

Table room
roomid 	number 	type
1 	     11 	    single
2 	     12 	    single
3 	     13 	    singleen
4 	     14 	    singleen
5 	     15 	    double
6 	     16 	    double
7 	     17 	    doubleen
8 	     18 	    doubleen
9 	     19 	    doubleen
Last edited by feyd on Thu Feb 17, 2005 8:46 am, edited 1 time in total.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

usually, this can be solved with subqueries

select *
from foo
where roomid not in (select here query that looks up taken rooms)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

completely untested

Code: Select all

SELECT
  rm.*
FROM
  rooms rm
LEFT JOIN
  roombooking rb
ON
  rm.roomid = rb.roomid
LEFT JOIN
  booking b
ON
  b.bookingid = rb.bookingid
WHERE
  ($arrival NOT BETWEEN b.bookedfrom AND b.bookedto)
  AND
  ($depart NOT BETWEEN b.bookedfrom AND b.bookedto)
I'd like to request your future posts include a SQL export of the table structure and data so it may be tested a bit.
Timmymna
Forum Newbie
Posts: 5
Joined: Sun Feb 13, 2005 7:24 am
Location: UK

Post by Timmymna »

Here is an SQL export.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I asked you to post the export, not post a link to it..

The export:

Code: Select all

-- phpMyAdmin SQL Dump
-- version 2.6.0-pl3
-- http://www.phpmyadmin.net
--
-- Host: localhost:3306
-- Generation Time: Feb 17, 2005 at 03:16 PM
-- Server version: 4.1.7
-- PHP Version: 4.3.9
--
-- Database: `shepdene`
--
-- --------------------------------------------------------
--
-- Table structure for table `booking`
--
CREATE TABLE `booking` (
  `bookingid` int(11) NOT NULL auto_increment,
  `checked` enum('yes','no') NOT NULL default 'no',
  `customerid` int(11) NOT NULL default '0',
  `bookedfrom` date NOT NULL default '0000-00-00',
  `bookedto` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`bookingid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=29 ;
--
-- Dumping data for table `booking`
--
INSERT INTO `booking` VALUES (14, 'yes', 53, 0x323030372d30312d3031, 0x323030372d30322d3032);
INSERT INTO `booking` VALUES (15, 'yes', 54, 0x323030362d30312d3031, 0x323030362d30322d3032);
INSERT INTO `booking` VALUES (16, 'yes', 52, 0x323030342d30312d3031, 0x323030342d30322d3032);
INSERT INTO `booking` VALUES (17, 'no', 51, 0x323030352d30312d3031, 0x323030352d30322d3032);
-- --------------------------------------------------------
--
-- Table structure for table `customer`
--
CREATE TABLE `customer` (
  `customerid` int(11) NOT NULL auto_increment,
  `title` text NOT NULL,
  `name` text NOT NULL,
  `surname` text NOT NULL,
  `numname` text NOT NULL,
  `street` text NOT NULL,
  `town` text NOT NULL,
  `county` text NOT NULL,
  `postcode` text NOT NULL,
  `teleday` text NOT NULL,
  `teleeve` text NOT NULL,
  `email` text NOT NULL,
  PRIMARY KEY  (`customerid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=80 ;
--
-- Dumping data for table `customer`
--
INSERT INTO `customer` VALUES (51, 'Mr', 'John', 'Doe', '01', 'Street01', 'Town01', 'County01', 'AB01 1AA', '0123456789', '0123456789', 'johndoe@example.com');
INSERT INTO `customer` VALUES (52, 'Mrs', 'Jane', 'Doe', '02', 'Street02', 'Town02', 'County02', 'AB02 2AA', '0123456789', '0123456789', 'janedoe@example.com');
INSERT INTO `customer` VALUES (53, 'Miss', 'Jess', 'Doe', '03', 'Street03', 'Town03', 'County03', 'AB03 3AA', '0123456789', '0123456789', 'jessdoe@example.com');
INSERT INTO `customer` VALUES (54, 'Other', 'James', 'Doe', '04', 'Street04', 'Town04', 'County04', 'AB04 4AA', '0123456789', '0123456789', 'jamesdoe@example.com');
-- --------------------------------------------------------
--
-- Table structure for table `room`
--
CREATE TABLE `room` (
  `roomid` int(11) NOT NULL auto_increment,
  `number` int(11) NOT NULL default '0',
  `type` enum('single','singleen','double','doubleen') NOT NULL default 'single',
  `inuse` enum('yes','no') NOT NULL default 'yes',
  PRIMARY KEY  (`roomid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=26 ;
--
-- Dumping data for table `room`
--
INSERT INTO `room` VALUES (1, 11, 'single', 'yes');
INSERT INTO `room` VALUES (2, 12, 'single', 'yes');
INSERT INTO `room` VALUES (3, 13, 'singleen', 'yes');
INSERT INTO `room` VALUES (4, 14, 'singleen', 'yes');
INSERT INTO `room` VALUES (5, 15, 'double', 'yes');
INSERT INTO `room` VALUES (6, 16, 'double', 'yes');
INSERT INTO `room` VALUES (7, 17, 'doubleen', 'yes');
INSERT INTO `room` VALUES (8, 18, 'doubleen', 'yes');
INSERT INTO `room` VALUES (9, 19, 'doubleen', 'yes');
-- --------------------------------------------------------
--
-- Table structure for table `roomsbooked`
--
CREATE TABLE `roomsbooked` (
  `bookingid` int(11) NOT NULL default '0',
  `roomid` int(11) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `roomsbooked`
--
INSERT INTO `roomsbooked` VALUES (14, 2);
INSERT INTO `roomsbooked` VALUES (15, 5);
INSERT INTO `roomsbooked` VALUES (16, 8);
INSERT INTO `roomsbooked` VALUES (16, 7);
INSERT INTO `roomsbooked` VALUES (17, 6);
INSERT INTO `roomsbooked` VALUES (17, 1);
INSERT INTO `roomsbooked` VALUES (17, 9);
Timmymna
Forum Newbie
Posts: 5
Joined: Sun Feb 13, 2005 7:24 am
Location: UK

Post by Timmymna »

You have my apologies, it was exported as a file so I presumed you wanted the file rather than just the text. I will remember for future reference
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

thank you. :)
Post Reply