SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 30
  1. #1
    SitePoint Member
    Join Date
    Jan 2005
    Location
    the hague
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    reservation system query/script

    I've been working on a reservation system for some time now. It's almost done but one annoying problem. I've got 2 tables: (I'll just put what is needed for the query)

    reservations
    ---------------
    room_id
    room_type_id
    date_from
    date_till

    bedrooms
    ---------------
    room_id
    room_type_id

    When a reservation is made, all details go into the reservations table. In the
    bedrooms table you have the room_type_id (single room is 10, double room is 20, etc) You can have like 2 single rooms, 3 double, etc. The room_id is unique so I can keep count of the rooms.

    When the user selects a reservation, the dates and the room_type_id is passed on to this script:

    PHP Code:
    function check_res($from$till$type) {

    if (
    $till <= $from) { //Check if till date is before from date.
             
    $message "E";      
          }
          
          else { 
    // check if room is available
          
     
    $check_room "SELECT bedrooms.room_id FROM bedrooms, reservations 
    WHERE bedrooms.room_type_id = '
    $type'   
    AND bedrooms.room_id NOT IN reservations WHERE
    '
    $from' <= reservations.date_from AND '$till' >= reservations.date_from
    OR '
    $from' <= reservations.date_till AND '$till' >= reservations.date_till
    OR '
    $from' >= reservations.date_from AND '$till' <= reservations.date_till)"
                            
    $get_room mysql_query($check_room) or die (mysql_error());
                           
    if (
    mysql_num_rows($get_room)) { // room available return room_id
                         
          
    $list_room mysql_fetch_row($get_rooms);
          
    $message $list_room[0]; 
                         }
                         
            else { 
    // room occupied
                
    $message "F";
                         }

          
          }
    // end check dates
                 
    return $message;

    And now for the problem:

    1. I just rewrote this script, the old one I had was to complicated. This script
    gives me a syntax error near 'reservations WHERE...' I can't figure out what's wrong with the syntax.

    2. I still haven't figured how I can check in the bedrooms and reservations table if a room of the given room_type_id is available.


    Point case: The script should check first if I can use the same room that is already in the reservations table if they don't have the same date. If they do have the same date it should check a room_id from the bedrooms table that isn't already in the reservations table. I'm using mysql version 4.0.18

    I hope you guys can make sense from this post, I've asked some people to help me but they couldn't figure it out either. I've posted on other forums, but also not much help. I have no clue anymore as what could work.

  2. #2
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    Parry Sound, ON
    Posts
    725
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    For #1: you've got two where clauses and I think you meant to have a subselect.

  3. #3
    SitePoint Member
    Join Date
    Jan 2005
    Location
    the hague
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I wanted to use a subquery but the mysql version I have doesn't support subqueries.

  4. #4
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    Parry Sound, ON
    Posts
    725
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well then you can't have two where clauses

  5. #5
    SitePoint Member
    Join Date
    Jan 2005
    Location
    the hague
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok that I understand.... Due to limitations of mysql lesser then 4.1 I can't think of a way to this query without subqueries or multiple queries without it getting to complicated.

    I have searched the database of Sitepoint forums many times but for this one problem I'm really stuck. I've tried the complicated way of having multiple queries but it doesn't work.

    So anyone has any idea how I should tackle this query or script?

  6. #6
    SitePoint Wizard Chris82's Avatar
    Join Date
    Mar 2002
    Location
    Osnabrück
    Posts
    1,003
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can "imitate" a subselect with two queries. First you execute the part which would go into the subquery and store the results as a comma separated list and then use the list in the original query

    PHP Code:
    $result mysql_query("SELECT room_id FROM reservations WHERE
    '
    $from' <= reservations.date_from AND '$till' >= reservations.date_from
    OR '
    $from' <= reservations.date_till AND '$till' >= reservations.date_till
    OR '
    $from' >= reservations.date_from AND '$till' <= reservations.date_till");

    $list '';
    while (
    $row mysql_fetch_row($result)) {
       
    $list .= ', ' $row[0];
    }
    // don't know if this will add a ", " at the end, though
    // echo $list;

    $check_room mysql_query("SELECT bedrooms.room_id FROM bedrooms
    WHERE bedrooms.room_type_id = '
    $type'   
    AND bedrooms.room_id NOT IN (
    $list)");

    // this should now contain only the valid records. 

  7. #7
    SitePoint Member
    Join Date
    Jan 2005
    Location
    the hague
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Chris82, your way seems the best I've tried until now. I adjusted the script the way I need it:

    PHP Code:
    //check first if room_id of given room_type is occupied on date
    $date mysql_query("SELECT room_id FROM reservations WHERE room_type_id = '$type' AND 
         '
    $from' <= date_from AND '$till' >= date_from
    OR '
    $from' <= date_till AND '$till' >= date_till
    OR '
    $from' >= date_from AND '$till' <= date_till");

          
    $list '';
          while (
    $row mysql_fetch_row($date)) {
          
    $list .= ', ' .$row[0];
                                  }

       
    $check_date mysql_query($date) or die (mysql_error());
                    
    if (
    mysql_num_rows($check_date)) { //room is already reserved. check alternative 

            //select a room that is not in reservations table
          
    $check_room mysql_query("SELECT room_id FROM bedrooms
                      WHERE room_type_id = '
    $type'   
                      AND room_id NOT IN '
    $list'"); 


          
    $get_room mysql_query($check_room) or die (mysql_error());
                           
        if (
    mysql_num_rows($get_room)) { // room available
                         
               
    $list_room mysql_fetch_row($get_rooms);
                
    $message $list_room[0]; 
                         }
                         
                         else { 
    // room occupied
                            
    $message "F";
                         }
                         
                         }
    //end if alternative
                  
               //no records where found in reservations table, all is free
             
    else { echo "all rooms free"; } 
    The script makes sense to me but I get the following error:

    You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'Resource id #5' at line 1

    The line number isn't any help as the script is included from a functions page. I'll keep trying with this method see if I can get it working, any more suggestions are welcome.

  8. #8
    SitePoint Wizard Chris82's Avatar
    Join Date
    Mar 2002
    Location
    Osnabrück
    Posts
    1,003
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The "Not in" part works on a set which needs to be inside brackets:

    PHP Code:
    $check_room mysql_query("SELECT room_id FROM bedrooms 
                      WHERE room_type_id = '
    $type'    
                      AND room_id NOT IN (
    $list)"); 

  9. #9
    SitePoint Member
    Join Date
    Jan 2005
    Location
    the hague
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I got rid of the error finally...

    This is my script now:

    PHP Code:
    function check_res($from$till$type) {

    if (
    $till <= $from) { //Check if till date is before from date. first if
             
    $message "E";      
          }
          
          else {            
    $date mysql_query("SELECT room_id FROM reservations WHERE room_type_id = '$type' AND 
         
    $from' <= date_from AND '$till' >= date_from
    OR '
    $from' <= date_till AND '$till' >= date_till
    OR '
    $from' >= date_from AND '$till' <= date_till");

          
    $list '';
          while (
    $row mysql_fetch_row($date)) {
          
    $list .= ', ' .$row[0];
                                  }

    if (
    mysql_num_rows($date)) { //room is already reserved. check alternative 

        
    $get_room mysql_query("SELECT room_id FROM bedrooms
        WHERE room_type_id = '
    $type' AND room_id NOT IN ('$list')");

    // this should now contain only the valid records. 
                           
       
    if (mysql_num_rows($get_room)) { // room available

       
    $list_room mysql_fetch_row($get_room);
                              
          echo 
    "room IS available\n"//room_id is already in resrevations table but available on date of choice
                         
    }
                                   
     else { 
    // room occupied
                            
         
    echo "room NOT available\n"//all room_id of given type are occupied on given date
              
    }
                         
                    }
    //end if alternative
                          
    else { echo "all rooms free\n"; } //all room_id of given type is still available

          
          
    }// end else check dates
                 
    return $message;

    When all room_id of the given type are occupied on chosen date it still returns that the 'room IS available' :'(

  10. #10
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,807
    Mentioned
    158 Post(s)
    Tagged
    3 Thread(s)
    Does $list output as the expected string?
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  11. #11
    SitePoint Member
    Join Date
    Jan 2005
    Location
    the hague
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Spike $list outputs a faulty id....

    when I try to reservate a room which I know is occupied and only has one:

    Example there is only one Single Suite...I try to reserve it on a date that it's already taken and instead that $list outputs room_id 1 it outputs room_id 1,2

    It's supposed to output 1 and that's it

  12. #12
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,807
    Mentioned
    158 Post(s)
    Tagged
    3 Thread(s)
    The reason I ask is that mysql_fetch_row will return only one row so there is no need for a while loop. The result is already an array!

    Perhaps something like
    PHP Code:
    $list mysql_fetch_row($date); 
         
    if (
    mysql_num_rows($date)) { //room is already reserved. check alternative 

        
    $get_room mysql_query("SELECT room_id FROM bedrooms 
        WHERE room_type_id = '
    $type' AND room_id != '$list'"); 
    Not sure what it will do in relation to the rest of your script but it might be the reason why you are getting room_id 1,2.

    Spike
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  13. #13
    SitePoint Member
    Join Date
    Jan 2005
    Location
    the hague
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok I tried your way, but instead of getting 1,2 I'll get Array. Meanwhile I narrowed down the source that the script won't work to this piece:

    PHP Code:
       $list '';
       while (
    $row mysql_fetch_row($date)) {
       
    $list .= ', ' .$row[0];
                 }

    if (
    mysql_num_rows($date)) { //room is already reserved. check alternative 
         
            
    $get_room mysql_query("SELECT room_id FROM bedrooms
             WHERE room_type_id = '
    $type' AND room_id != '$list'");

           
    // this should now contain only the valid records. 
            
    if (mysql_num_rows($get_room)) { // room available  //ERROR ON THIS LINE
                         
                
    $list_room mysql_fetch_row($get_room);
                              
              echo 
    "room IS available\n $list"//room_id is already in resrevations table but
                                                          //available on date of choice
                         


  14. #14
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,807
    Mentioned
    158 Post(s)
    Tagged
    3 Thread(s)
    Intead of
    PHP Code:
    $list ''
       while (
    $row mysql_fetch_row($date)) { 
       
    $list .= ', ' .$row[0]; 
                 } 
    try
    PHP Code:
    $list $row[0]; 
    SpikeZ
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  15. #15
    SitePoint Zealot saurab's Avatar
    Join Date
    Sep 2004
    Posts
    127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Ms_Hyde
    I got rid of the error finally...

    This is my script now:

    PHP Code:
    function check_res($from$till$type) {
     
    if (
    $till <= $from) { //Check if till date is before from date. first if
    $message "E"
    }
     
    else { 
    $date mysql_query("SELECT room_id FROM reservations WHERE room_type_id = '$type' AND 
    $from' <= date_from AND '$till' >= date_from
    OR '
    $from' <= date_till AND '$till' >= date_till
    OR '
    $from' >= date_from AND '$till' <= date_till");
     
    $list '';
    while (
    $row mysql_fetch_row($date)) {
    $list .= ', ' .$row[0];
    }
     
    if (
    mysql_num_rows($date)) { //room is already reserved. check alternative 
     
    $get_room mysql_query("SELECT room_id FROM bedrooms
    WHERE room_type_id = '
    $type' AND room_id NOT IN ('$list')");
     
    // this should now contain only the valid records. 
     
    if (mysql_num_rows($get_room)) { // room available
     
    $list_room mysql_fetch_row($get_room);
     
    echo 
    "room IS available\n"//room_id is already in resrevations table but available on date of choice
    }
     
    else { 
    // room occupied
     
    echo "room NOT available\n"//all room_id of given type are occupied on given date
    }
     
    }
    //end if alternative
     
    else { echo "all rooms free\n"; } //all room_id of given type is still available
     
     
    }// end else check dates
     
    return $message;

    When all room_id of the given type are occupied on chosen date it still returns that the 'room IS available' :'(
    Assuming that the logic part of the script is correct you might want to change this :

    $date = mysql_query("SELECT room_id FROM reservations WHERE
    room_type_id = '$type'
    AND '$from' <= date_from
    AND '$till' >= date_from
    OR

    '$from' <= date_till
    AND '$till' >= date_till
    OR

    '$from' >= date_from
    AND '$till' <= date_till");

    to:

    $date = mysql_query("SELECT room_id FROM reservations WHERE
    room_type_id = '$type'
    AND (
    ($from' <= date_from

    AND '$till' >= date_from)
    OR
    ('$from' <= date_till
    AND '$till' >= date_till)
    OR
    ('$from' >= date_from
    AND '$till' <= date_till")
    ) );

    I think if this doesnt work, change the grouping of ORs and ANDs with parantheses to suit your app logic and check if it is indeed representative of what you want done.
    I have a feeling that this is the place where you need to check for correctness.

    --saurab.


    Accepting Offers: NicheLabs.com - ExploringVOIP.com
    StableJob.com - AboutUniversities.com - Gemstone.co.in
    Assets.co.in - PropertyDealers.org - MortgageFirms.org
    eLearner.org - TravelResorts.info - TourstoAsia.com



  16. #16
    SitePoint Member
    Join Date
    Jan 2005
    Location
    the hague
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well did the easy way with $list, added brackets and simplified the date query:

    PHP Code:
    $date mysql_query("SELECT room_id FROM reservations WHERE room_type_id = '$type' AND 
    ('
    $from' <= date_till AND '$till' >= date_from)");

              
    $list $row[0];       
                                 
    if (
    mysql_num_rows($date)) { //room is already reserved. check alternative 
       
        
    $get_room mysql_query("SELECT room_id FROM bedrooms
        WHERE room_type_id = '
    $type' AND room_id != '$list'");

                      
    // this should now contain only the valid records. 

                           
         
    if (mysql_num_rows($get_room)) { // room available  //ERROR ON THIS LINE
                         
          
    $list_room mysql_fetch_row($get_room);
                              
         echo 
    "room IS available\n"//room_id is already in resrevations table but
                                                          //available on date of choice
                         

    It still keeps giving room IS available while it's not.

  17. #17
    SitePoint Zealot saurab's Avatar
    Join Date
    Sep 2004
    Posts
    127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Ms_Hyde
    Well did the easy way with $list, added brackets and simplified the date query:

    PHP Code:
    $date mysql_query("SELECT room_id FROM reservations WHERE room_type_id = '$type' AND 
    ('
    $from' <= date_till AND '$till' >= date_from)");
     
    $list $row[0]; 
     
    if (
    mysql_num_rows($date)) { //room is already reserved. check alternative 
     
    $get_room mysql_query("SELECT room_id FROM bedrooms
    WHERE room_type_id = '
    $type' AND room_id != '$list'");
     
    // this should now contain only the valid records. 
     
     
    if (mysql_num_rows($get_room)) { // room available //ERROR ON THIS LINE
     
    $list_room mysql_fetch_row($get_room);
     
    echo 
    "room IS available\n"//room_id is already in resrevations table but
    //available on date of choice

    It still keeps giving room IS available while it's not.
    On closer investigation of your code, there is a mistake I think :

    1. see this :
    PHP Code:
    date mysql_query("SELECT room_id FROM reservations WHERE room_type_id = '$type' AND 
    ('
    $from' <= date_till AND '$till' >= date_from)");
     
    $list $row[0]; 
    here your intention is to get all those room ids which satisfy the criteria given in the where clause. right ?
    now $list stores only the first such room id found by the query and not all ....
    you need to loop through the results ....
    and push all the IDs into an array using something like an array_push().. like this :

    PHP Code:
    $list = array(); // initialise the array before the while loop
    while($row mysql_fetch_array($resultMYSQL_NUM)) {
     
    array_push($list_of_room_ids$row[0]);

    now this array $list holds all the room ids matched by the where clause in your query.

    2. see below:

    PHP Code:
    $get_room mysql_query("SELECT room_id FROM bedrooms
    WHERE room_type_id = '
    $type' AND room_id != '$list'"); 
    Here, as per my understanding, you want to select all those room ids which are not in the list of room ids in the $list array (and where type = etc etc ..)

    Now, if $list is an array you cant use the != operator .....
    Moreover, try to print out the $list array and see what you get .. and then try to execute the second query without the second condition in the where clause ( the condition in which you test room_id != '$list' ) and see ... this kind of debugging always helps.....
    Accepting Offers: NicheLabs.com - ExploringVOIP.com
    StableJob.com - AboutUniversities.com - Gemstone.co.in
    Assets.co.in - PropertyDealers.org - MortgageFirms.org
    eLearner.org - TravelResorts.info - TourstoAsia.com



  18. #18
    SitePoint Member
    Join Date
    Jan 2005
    Location
    the hague
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by saurab

    Here, as per my understanding, you want to select all those room ids which are not in the list of room ids in the $list array (and where type = etc etc ..)

    Now, if $list is an array you cant use the != operator .....
    Moreover, try to print out the $list array and see what you get .. and then try to execute the second query without the second condition in the where clause ( the condition in which you test room_id != '$list' ) and see ... this kind of debugging always helps.....
    Ok I tried the piece of code you give me. the $list seems to work but again while I print $list I get Array again. If I change the != in the query to NOT IN I get an error in the query...

    But you are right, I want to select room_id that is not in the array.

  19. #19
    SitePoint Zealot saurab's Avatar
    Join Date
    Sep 2004
    Posts
    127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is your db schema .....

    reservations
    ---------------
    room_id
    room_type_id
    date_from
    date_till

    bedrooms
    ---------------
    room_id
    room_type_id

    If you change your db schema, it could be made easier for you ....

    new schema :

    reservations
    -------------
    reservation_id
    room_id
    date_from
    date_till

    rooms
    ------
    room_id
    room_type_id
    room_reservation_status (enum('Y', 'N')


    now try to think of the queries .. i think they will be much simpler with this schema (notice the room_reservation_status attribute). The moment a room is reserved 2 things happen : a new row in the reservations table is created and in thw rooms table the row with the particular room_id has its reservation status changed from 'N' to 'Y'

    this will make the queries much simpler when you need to check for rooms that are unreserved no need for joins..
    when a room goes vacnt, again do 2 things delete the corresponding row from the reservation table and change the reservation status in the rooms table to 'N'
    to make this 2-step process "atomic" , use transactions ....
    if you dont know transactions , search on google for mysql + transactions

    --saurab
    Accepting Offers: NicheLabs.com - ExploringVOIP.com
    StableJob.com - AboutUniversities.com - Gemstone.co.in
    Assets.co.in - PropertyDealers.org - MortgageFirms.org
    eLearner.org - TravelResorts.info - TourstoAsia.com



  20. #20
    SitePoint Member
    Join Date
    Jan 2005
    Location
    the hague
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I thought of that already but it's not logic. A lot of people bump into that.

    Imagine this case:

    room_id 1 a Single Suit and room_id 2 a Single Suit. There are 2 Single Suit.

    room_id 1 is already reserved lets say next week 31 jan till 5 febr.

    I wan to reserve a Single Suit from 6 febr. till 9 febr.

    So in fact I can also have room_id 1 because they are wanted on different days. But if I wanted it the same dates or in between then it would have to asign me room_id 2 which is free.

    If I just add room_reservation_status (enum('Y', 'N') it would not make any sense.

    I hope I explained it correct.

  21. #21
    SitePoint Zealot saurab's Avatar
    Join Date
    Sep 2004
    Posts
    127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Ms_Hyde
    I thought of that already but it's not logic. A lot of people bump into that.

    Imagine this case:

    room_id 1 a Single Suit and room_id 2 a Single Suit. There are 2 Single Suit.

    room_id 1 is already reserved lets say next week 31 jan till 5 febr.

    I wan to reserve a Single Suit from 6 febr. till 9 febr.

    So in fact I can also have room_id 1 because they are wanted on different days. But if I wanted it the same dates or in between then it would have to asign me room_id 2 which is free.

    If I just add room_reservation_status (enum('Y', 'N') it would not make any sense.

    I hope I explained it correct.
    Oh okay now it makes sense to me ..... anyway best of luck with your work.
    Accepting Offers: NicheLabs.com - ExploringVOIP.com
    StableJob.com - AboutUniversities.com - Gemstone.co.in
    Assets.co.in - PropertyDealers.org - MortgageFirms.org
    eLearner.org - TravelResorts.info - TourstoAsia.com



  22. #22
    SitePoint Addict toggg's Avatar
    Join Date
    Jan 2005
    Location
    Auvergne/France
    Posts
    253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    better build $list

    Bonjour Ms_Hyde
    Try that:
    PHP Code:
    function check_res($from$till$type) {

    if (
    $till <= $from) { //Check if till date is before from date. first if
        
    $message "E";
    }

    else {
    $date mysql_query("SELECT room_id FROM reservations WHERE room_type_id = '$type' AND
    $from' <= date_from AND '$till' >= date_from
    OR '
    $from' <= date_till AND '$till' >= date_till
    OR '
    $from' >= date_from AND '$till' <= date_till");

    $list "'Fake romm id'"// just case all room are free.
    while ($row mysql_fetch_row($date)) {
        
    $list .=  ", '{$row[0]}'";
    }

    $get_room mysql_query("SELECT room_id FROM bedrooms
    WHERE room_type_id = '
    $type' AND room_id NOT IN ($list)");

    // this should now contain only the valid records.

    if (mysql_num_rows($get_room)) { // room available

    $list_room mysql_fetch_row($get_room);

    echo 
    "room IS available\n"//room_id is already in resrevations table but available on date of choice
    }

    else { 
    // room occupied

    echo "room NOT available\n"//all room_id of given type are occupied on given date
    }

    }
    //end if alternative

    else { echo "all rooms free\n"; } //all room_id of given type is still available


    }// end else check dates

    return $message;

    This 'fake room' is just there to avoid the case where everything is free: should be something not existing in the ID.
    Your transaction should be after you found a room.
    Begin, read if still free with table lock, insert record, commit;

    One other thing, not PHP, but you should have a rotation on the rooms, not to give allways the same room.
    It's something like choosing this one that was not hired for a max time.

    You could also have a structure where all days for all rooms are stored, just marked free or reserved. You had a better life and could more pinche packen
    bertrand Gugger toggg.com linux, PHP, Auvergne/France open source

  23. #23
    SitePoint Member
    Join Date
    Jan 2005
    Location
    the hague
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well toggg, your $list system seems to make a difference. I'm almost there just 2 errors and then it should be working. Here goes my current script:

    PHP Code:
    function check_res($from$till$type) {

    if (
    $till <= $from) { //Check if till date is before from date. first if
             
    $message "E";      
       }
          
    else { 

    $date mysql_query("SELECT room_id FROM reservations WHERE room_type_id = '$type' AND 
    ('
    $from' <= date_till AND '$till' >= date_from)");

                   
    $list "10001"// just case all room are free.
          
    while ($row mysql_fetch_row($date)) {
                   
    $list .=  ", '{$row[0]}'";
                                        }
                                 
          if (
    mysql_num_rows($date)) { //room is already reserved. check alternative 

       
    $get_room mysql_query("SELECT room_id FROM bedrooms
        WHERE room_type_id = '
    $type' AND room_id != '($list)'");

      
    // this should now contain only the valid records. 

                           
    if (mysql_num_rows($get_room)) { // room available  
                         
          
    $list_room mysql_fetch_row($get_room);
                              
    echo 
    "room IS available\n $list"//room_id is already in reservations table but available on date of choice
                         
    }
                                   
         else { 
    // room occupied
                            
        
    echo "room NOT available\n"//all room_id of given type are occupied on given date
                         
    }
                         
                         }
    //end if alternative
                          
    else { echo "all rooms free\n"; } //all room_id of given type is still available

          
          
    }// end else check dates
                 
    return $message;

    It now gives me correctly 'room NOT available' and 'all rooms free'

    The 2 errors still hapening:

    When it checks the $list while I get:
    mysql_num_rows(): supplied argument is not a valid MySQL result

    And when there is one room of the given type reserved on the same date and stilll 2 other rooms available it won't acknowledge the other 2 rooms of the same type that are free on the date of choice.

    Getting very close to getting the script 100% correct...

  24. #24
    SitePoint Addict toggg's Avatar
    Join Date
    Jan 2005
    Location
    Auvergne/France
    Posts
    253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    that should be NOT IN and without quotes:
    PHP Code:
       $get_room mysql_query("SELECT room_id FROM bedrooms
        WHERE room_type_id = '
    $type' AND room_id NOT IN ($list)"); 
    will give NOT IN ('1','5') for instance.
    bertrand Gugger toggg.com linux, PHP, Auvergne/France open source

  25. #25
    SitePoint Member
    Join Date
    Jan 2005
    Location
    the hague
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Solved!

    PHP Code:
    $get_room mysql_query("SELECT room_id FROM bedrooms
    WHERE room_type_id = '
    $type' AND room_id != '($list)'"); 
    Should be:

    PHP Code:
    $get_room mysql_query("SELECT room_id FROM bedrooms
    WHERE room_type_id = '
    $type' AND room_id NOT IN ($list)"); 
    The only thing left is:

    I need to have a room_id returned to use for the reservation. Thus when room IS available or all room are free I need to get the/a room_id of the free room .

    EDIT: toggg I posted same time as you I realized that that was indeed the problem. Now only the last problem of returning room_id of free room.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •