SQL error

[prolife]

Good day everyone, my SQL is giving yet another result i dont like, its duplicating results. Please anyone with an iidea on how to get this solved?

Below is my sql:

 

SELECT `rooms`.* , `order`.* FROM `rooms` INNER JOIN `order` ON `order`.`room-id`!=`rooms`.`roomid` AND `rooms`.`cond`='not taken' AND `order`.`checkin` !='$checkin' AND `order`.`checkout` !='$checkout'.

 

In brief the results are to be displayed as long as the 'checkin' and 'checkout' dates are not already recorded or stored in the order table and also that the condition or maybe status of the room is 'not taken '

 

Thanks

Edited September 19, 2013 by prolife

[prolife]

actually the code is meant to check the availability of a room or rooms; such that once the user types the checkin and checkout date and clicks search, he gets a result of rooms that are avalable. I will also appreciate if anyone has a better code using the same fields. Thanks guys, you are the best!

[vinny42]

 

ts duplicating results. 

 

Because you can only join tables on the basis that some field has the same value, not on having a different value.

What you *can* do is LEFT JOIN on records that *do* have the same value. If there are no records that match, LEFT JOIN will use NULL values for the missing data, and you can check for NULL in either SQL or your application.

[Barand]

In the diagram below, rooms E and F are available between the requested checkin and checkout dates. The test for a room being booked for all or part of the requested period is

 

S < checkout AND F > checkin

 

A LEFT JOIN between all rooms and rooms booked will give those available

                       requested                    requested
                        checkin                      checkout
                           |                            |
Room A     S------------------F                         |
Room B                     |     S---------------F      |
Room C                     |                         S-------------F
Room D              S----------------------------------------F
Room E     S------------F  |                            |
Room F                     |                            | S-------------F

[prolife]

@vinny42 thanks, i now have a better understanding of what Join is all about,@ Barand thanks for your contribution too. 

Now the question is how do i get this solved?

Here is a proper explanation of what i want to do maybe anyone could help me come up with an SQL to get the problem solved

 

 

for the ROOMS table we have-: [roomid,room-name,price,catid,pics,rooom-descript,cond]

 

For ORDER table we have-: [userid,orderid,room-id,catid,status,checkin,checkout,nokids,noadults,order-date,cond]

 

This is exactly what i want to do (i want to check for the availability of a room) So i SELECT all from ROOMS, roomid from ORDERS provided checkin

 

date and the checkout date provided by the user doesnt equal any checkin or checkout date already stored into the ORDERS table by another user

 

and also display the rooms in ROOMS table whose roomid are not present in the ORDERS TABLE

[vinny42]

What you want to do is find rooms where the checkin and checkout dates don't overlap.

Normally in SQL you'd just do: WHERE NOT (checkin,checkout) OVERLAPS ($newchecking, $newcheckout)

but MySQL doesn't support OVERLAPS so you'll have to write up the conditions that define when there is an overlap.

I suggest that you gogle for this, you're probably not the first to want this.

[Barand]

Based on my previous reply,

SELECT r.roomid, r.`room-name`, r.price, r.`room-descript`
FROM rooms r
LEFT JOIN order o
    ON r.roomid = o.`room-id` AND o.checkin < '$checkout' AND o.checkout > '$checkin'
WHERE o.`room-id` IS NULL

DO NOT use "-" in column names (SQL treats them as minus signs). Use underscores "_"

[prolife]

Alright guys thanks for your efforts. Barrand and vinny42,thanks alot. I already sorted it out; I came up with an alternative. Thanks once again!

[vinny42]

Out of curiosity and for possible future visitors of this topic;  what was the alternative you ended up using?

[prolife]

after taking some time to think, it dawned on me that my approach was very wrong; i was just being unecessarilly complex. In brief all i did was to make some changes in my table, such that once a room is taken, the field with that particular "ROOMID"  is autamatically updated as "UNAVAILABLE" meaning when making a search all i do is ("SELECT * FROM `rooms` WHERE `status`='AVAILABLE' "). Thats it very simple i guess?

[prolife]

I have yet another error guys,im currently working on an hotel App i'm almost done so i'm doing some debugging. I have a page where the hotel administrator can edit gallery, now when i was done editing i clicked save, it actually updated and stored it into the database but it displayed the error below in the text field

 

 

<br />
<font size='1'><table class='xdebug-error' dir='ltr' border='1' cellspacing='0' cellpadding='1'>
<tr><th align='left' bgcolor='#f57900' colspan="5"><span style='background-color: #cc0000; color: #fce94f; font-size: x-large;'>( ! )</span> Notice: Uninitialized string offset: 0 in C:\wamp\www\hot\apps\admin-edit-gallery.php on line <i>53</i></th></tr>
<tr><th align='left' bgcolor='#e9b96e' colspan='5'>Call Stack</th></tr>
<tr><th align='center' bgcolor='#eeeeec'>#</th><th align='left' bgcolor='#eeeeec'>Time</th><th align='left' bgcolor='#eeeeec'>Memory</th><th align='left' bgcolor='#eeeeec'>Function</th><th align='left' bgcolor='#eeeeec'>Location</th></tr>
<tr><td bgcolor='#eeeeec' align='center'>1</td><td bgcolor='#eeeeec' align='center'>0.0038</td><td bgcolor='#eeeeec' align='right'>391904</td><td bgcolor='#eeeeec'>{main}(  )</td><td title='C:\wamp\www\hot\apps\admin-edit-gallery.php' bgcolor='#eeeeec'>..\admin-edit-gallery.php<b>:</b>0</td></tr>
</table></font>
 

 

Do you guys know what could have gone wrong? Cheers everyone!

[prolife]

Hello

 

please i dont know what went wrong ;this SQL is meant to count provided that this conditions are true.

 

i want to count the total number of rooms that were booked for in a particular month.

Please what is the right syntax to use when selecting from two different tables and then counting the total number of occurance.

 

Here is the SQL i came up with but its messig up.

SELECT  (SELECT COUNT(*) FROM `rooms`) (SELECT COUNT(*) FROM `order`) AS `total_rooms` FROM `rooms` WHERE `order`.`checkin`>'$real_date' AND `rooms`.`cond` ='available'