Jump to content

room availability not functioning...someone please give guidance..


pollysal

Recommended Posts

i have just create 4 tables like below :

 

CREATE TABLE customer(

customerID INT NOT NULL AUTO_INCREMENT ,

name VARCHAR( 30 ) ,

address VARCHAR( 30 ) ,

tel_no INT( 15 ) ,

email VARCHAR( 30 ) ,

PRIMARY KEY (customerID)

) ENGINE=INNODB;

 

 

CREATE TABLE roomtype(

roomtypeID INT NOT NULL AUTO_INCREMENT ,

roomtype VARCHAR( 30 ) ,

roomprice INT( 30 ) ,

roombed INT( 15 ) ,

PRIMARY KEY ( roomtypeID )

) ENGINE=INNODB;

 

CREATE TABLE rooms(

roomID INT NOT NULL AUTO_INCREMENT ,

roomtypeID varchar( 30 ) ,

room_no INT( 15 ) ,

PRIMARY KEY ( roomID ) ,

FOREIGN KEY ( roomtypeID ) REFERENCES roomtype( roomtypeID ) ON UPDATE CASCADE ON DELETE CASCADE

) ENGINE = INNODB

 

 

CREATE TABLE booking(

bookingID INT NOT NULL AUTO_INCREMENT ,

checkin DATETIME,

checkout DATETIME,

nights INT( 10 ) ,

totalprice INT( 100 ) ,

customerID INT,

roomID INT,

PRIMARY KEY ( bookingID ) ,

FOREIGN KEY ( customerID ) REFERENCES customer( customerID ) ,

FOREIGN KEY ( roomID ) REFERENCES rooms( roomID ) ON UPDATE CASCADE ON DELETE CASCADE

) ENGINE = INNODB

 

i really got no idea how to only display the roomtype and roomprice  from the table roomtype. I do really hope someone can help me as i have spent 5 hours only for this one query (yes..i admit i'm not talented in this stuff..), so please,if there's anyone can give any ideas for me to solve this...

i do appreciate it so much...

 

below is the query that i'm working on that never success :

 

select distinct roomtype, roomprice from roomtype where romtypeID IN (

select roomtypeID, roomID from rooms where roomID NOT IN (

select roomID froom booking where checkin>="2010-04-01" AND checkout<="2010-04-06"))

 

when i test it at phpmyadmin, the problem comes from the outter select which is the part "select distinct...".

when i tested it, the subselect works fine..the problems comes from the select distinct part

Link to comment
Share on other sites

It may help explaining what you want to query?

 

You may for example use a query like:

 

SELECT checkin, nights, totalprice
  FROM booking b

JOIN customer c USING customerID
WHERE c.name = 'John'
  AND day(checkin) = day(CURRENT_DATE);

 

To query all checkin's for today under the name John

 

Ofcourse the queries entirely depend on your business requirements/rules

Link to comment
Share on other sites

//then, at this part- the end part, it will only show the availability room based on the roomtype and roomprice only..

select distinct roomtype, roomprice from roomtype where romtypeID IN

 

//then, it wil show the available room based on the roomtypeID and roomID.

(select roomtypeID, roomID from rooms where roomID NOT IN

 

//at this part, i wanted to show the room availability,

(select roomID froom booking where checkin>="2010-04-01" AND checkout<="2010-04-06"))

Link to comment
Share on other sites

i would want the query to first, find the available room the checkin and checkout, then it will display the room available in the based on the table rooms that contains roomID and roomtypeID.

Then, from the roomtypeID(frm table room) as a foreign key to the roomtypeID in the table roomtype, it will display the roomprice and roomtype from roomtype.

 

The display of the roomprice and roomtype is really important as i want to sent the value of the roomtypeID to the next page of the php form by using id

Link to comment
Share on other sites

thanks to andrew and ignace..

i finally manage to get the result that i wanted based on my query.

i fix back my table especially at the part where the roomtypeID that have the conflict with the varchar.

This is the final result of my query that gave me the result that i wanted.

 

 

SELECT rt.roomtype, rt.roomprice

FROM roomtype rt

INNER JOIN rooms r ON rt.roomtypeID = r.roomtypeID

WHERE r.roomID NOT

IN (

 

SELECT roomID

FROM booking

WHERE checkin >= '2010-04-01'

AND checkout <= '2010-04-06'

)

GROUP BY rt.roomtypeID

 

 

thanks again to anyone who reply. cheers!!

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.