SitePoint Sponsor

User Tag List

Page 2 of 2 FirstFirst 12
Results 26 to 45 of 45
  1. #26
    SitePoint Enthusiast
    Join Date
    Jan 2005
    Location
    London UK, Tokyo, Japan
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    im using PHP4.1;



    hey i know how to change the dates, but the SQL query assumes that 2002-02-06 is the first night of stay when 2002-02-05 is... and it counts the enddate(2005-02-09) as a booked room...its back to front!

    it should check the dates from 2005-02-05 to 2005-02-08!

    any ideas?

  2. #27
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok - strange

    This is the data i have in my test table

    Code:
      -- 
      -- Table structure for table `room`
      -- 
      
      DROP TABLE IF EXISTS room;
      CREATE TABLE IF NOT EXISTS room (
        id int(11) NOT NULL auto_increment,
        bookdate date NOT NULL default '0000-00-00',
        enddate date NOT NULL default '0000-00-00',
        PRIMARY KEY  (id)
      ) TYPE=MyISAM AUTO_INCREMENT=4 ;
      
      -- 
      -- Dumping data for table `room`
      -- 
      
      INSERT INTO room (id, bookdate, enddate) VALUES (1, '2005-02-05', '2005-02-06');
      INSERT INTO room (id, bookdate, enddate) VALUES (2, '2005-02-05', '2005-02-07');
      INSERT INTO room (id, bookdate, enddate) VALUES (3, '2005-02-05', '2005-02-08');
    So, as I understand it, the result you want are

    2005-02-05 3 rooms booked
    2005-02-06 2 rooms booked
    2005-02-07 1 room booked
    2005-02-08 0 rooms booked

    Here is the complete code that I have just run on my test server (Apache 2.0.52, Windoze XP SP 2, PHP 4.3.8, MySQL 4.0.21)
    PHP Code:
      // your start and end dates
      
    $datein "2005-02-05";
      
    $dateout "2005-02-08";
      
      
    // room rates
      //   index is the number of rooms booked
      //   value is the cost of the room
      
    $rack_rate[0] = 20;            // 0 rooms booked rate = 20
      
    $rack_rate[1] = 30;            // 1 room booked rate = 30
      
    $rack_rate[2] = 40;            // 2 rooms booked rate = 40
      
      // array for holding totals for each day
      // the index into the array is the data in yy-mm-dd format
      // this can be changed to anything you want
      
    $totals = array();
      
      
    $sql 'SELECT'
           
    ' DATE_ADD(\'' $datein '\', interval i day) as thedate'
           
    ', COUNT(room.id) as bookings'
           
    ' FROM'
           
    ' integers'
           
    ' LEFT OUTER'
           
    ' JOIN room'
           
    ' ON DATE_ADD(\'' $datein '\', interval i day)'
           
    ' BETWEEN room.bookdate AND DATE_ADD(room.enddate, interval -1 day)'
           
    ' WHERE'
           
    ' i'
           
    ' BETWEEN 0 AND TO_DAYS(\'' $dateout '\') - TO_DAYS(\'' $datein '\')'
           
    ' GROUP'
           
    ' BY thedate'
           
    ' ORDER'
           
    ' BY thedate'
           
    ;
      
    /* ***** START DIAGNOSTIC **** */
      // display sql
      
    echo '<pre>';
      
    print_r($sql);
      echo 
    '</pre>';
      
    $result mysql_query($sql) or die ('Failed to execute ' $sql .  ' due to ' mysql_error());
      while (
    $row mysql_fetch_assoc($result))
      {
        
    $totals[$row['thedate']] = $row['bookings'];
      }
      
    mysql_free_result($result);
      
      
    /* ***** END DIAGNOSTIC **** */ 
      
      /* ***** START DIAGNOSTIC **** */
      // display totals
      
    echo '<pre>';
      
    print_r($totals);
      echo 
    '</pre>';
      
    /* ***** END DIAGNOSTIC **** */ 
      
      // print bookings for each date
      
    foreach ($totals as $date => $rooms_booked)
      {
        
    $str = (== $rooms_booked) ? ' room booked' ' rooms booked';
     
    $rate = (true == in_array($rooms_bookedarray_keys($rack_rate))) ? ' and price is ' $rack_rate[$rooms_booked] : ' but no rate set ';
        echo 
    $date ' has ' $rooms_booked $str $rate '<br />';
      } 
    and the results i got are

    HTML Code:
     SELECT DATE_ADD('2005-02-05', interval i day) as thedate, COUNT(room.id) as bookings FROM integers LEFT OUTER JOIN room ON DATE_ADD('2005-02-05', interval i day) BETWEEN room.bookdate AND DATE_ADD(room.enddate, interval -1 day) WHERE i BETWEEN 0 AND TO_DAYS('2005-02-08') - TO_DAYS('2005-02-05') GROUP BY thedate ORDER BY thedate
      
      Array
      (
      	[2005-02-05] => 3
      	[2005-02-06] => 2
      	[2005-02-07] => 1
      	[2005-02-08] => 0
      )
      
      2005-02-05 has 3 rooms booked but no rate set
      2005-02-06 has 2 rooms booked and price is 40
      2005-02-07 has 1 room booked and price is 30
      2005-02-08 has 0 rooms booked and price is 20

  3. #28
    SitePoint Enthusiast
    Join Date
    Jan 2005
    Location
    London UK, Tokyo, Japan
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up

    it was my fault, i didnt have "0" in my intergers table, sorry work perfect now.

    ok last question of the day, how do i sum up the rates and store total in a variable so i can pass it to another page?

    ie.
    $ratetotal=120;

    echo"<a href=nextpage.php?ratetotal=$ratetotal>";

    cheers ever so much swdev! you are a gem!

  4. #29
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the kind words

    Ok - more code

    PHP Code:
     // your start and end dates
     
    $datein "2005-02-05";
     
    $dateout "2005-02-08";
     
     
    // room rates
     //   index is the number of rooms booked
     //   value is the cost of the room
     
    $rack_rate[0] = 20;            // 0 rooms booked rate = 20
     
    $rack_rate[1] = 30;            // 1 room booked rate = 30
     
    $rack_rate[2] = 40;            // 2 rooms booked rate = 40
     
     // array for holding totals for each day
     // the index into the array is the data in yy-mm-dd format
     // this can be changed to anything you want
     
    $totals = array();
     
     
    $sql 'SELECT'
          
    ' DATE_ADD(\'' $datein '\', interval i day) as thedate'
          
    ', COUNT(room.id) as bookings'
          
    ' FROM'
          
    ' integers'
          
    ' LEFT OUTER'
          
    ' JOIN room'
          
    ' ON DATE_ADD(\'' $datein '\', interval i day)'
          
    ' BETWEEN room.bookdate AND DATE_ADD(room.enddate, interval -1 day)'
          
    ' WHERE'
          
    ' i'
          
    ' BETWEEN 0 AND TO_DAYS(\'' $dateout '\') - TO_DAYS(\'' $datein '\')'
          
    ' GROUP'
          
    ' BY thedate'
          
    ' ORDER'
          
    ' BY thedate'
          
    ;
     
    /* ***** START DIAGNOSTIC **** */
     // display sql
     
    echo '<pre>';
     
    print_r($sql);
     echo 
    '</pre>';
     
    $result mysql_query($sql) or die ('Failed to execute ' $sql .  ' due to ' mysql_error());
     while (
    $row mysql_fetch_assoc($result))
     {
       
    $totals[$row['thedate']] = $row['bookings'];
     }
     
    mysql_free_result($result);
     
     
    /* ***** END DIAGNOSTIC **** */ 
     
     /* ***** START DIAGNOSTIC **** */
     // display totals
     
    echo '<pre>';
     
    print_r($totals);
     echo 
    '</pre>';
     
    /* ***** END DIAGNOSTIC **** */ 
     
     
    $total_rate 0;
     
     
    // print bookings for each date
     
    foreach ($totals as $date => $rooms_booked)
     {
       
    $str = (== $rooms_booked) ? ' room booked' ' rooms booked';
     
       if (
    true == in_array($rooms_bookedarray_keys($rack_rate)))
       {
         
    $rate $rack_rate[$rooms_booked];
         
    $rate_str ' and price is ' $rate;
       }
       else
       {
         
    $rate 0;
         
    $rate_str ' but no rate set';
       }
     
       if (
    $rooms_booked)
       {
         
    $total_rate += $rate;
       }
     
       echo 
    $date ' has ' $rooms_booked $str $rate_str '<br>';
     }
     
     echo 
    'Total Rate is ' $total_rate '<br>';
     
     
    // build next page link with total rate as a query string
     
    echo '<a href="nextpage.php?ratetotal=' $total_rate '"> Next Page </a>'
    Note that this code generates valid HTML 4.01 Transitional markup

  5. #30
    SitePoint Enthusiast
    Join Date
    Jan 2005
    Location
    London UK, Tokyo, Japan
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation

    what even more code? are you fishing for more compliments! okay working well and perfect now...only if you check your earlier post and see the result when u tired it on your computer you will find that it outputs

    2005-02-05 3 rooms booked
    2005-02-06 2 rooms booked
    2005-02-07 1 room booked
    2005-02-08 0 rooms booked
    when it should output:
    2005-02-05 3 rooms booked
    2005-02-06 2 rooms booked
    2005-02-07 1 room booked
    as 2005-02-08 is checkout and they will not be staying that night so we cant charge them.

    hey just noticed the remark about my dodgy HTML! :'(

  6. #31
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Would I fish for compliments

    Ok - I see what you mean.

    This is easily fixed by either changing the $dateout variable or the 'BETWEEN' part of the SQL statement.

    When I calculate the total, I don't include a value for when there are no rooms booked.

  7. #32
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    swdev, nice job with all the code

    like i said, i don't do php (coldfusion is way easier) but your php is actually quite easy to read, and not nearly as convoluted and complex as most people's

    way to hang in there on this thread and see it to the conclusion
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

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

    Back to PHP solution

    Hi,
    as it's still in PHP forum let give PHP a try.
    I start with the very original room table, just I added a rate column as that stuff will at least be negociated.
    I don't need other table.
    Code:
      -- Table structure for table `room`
      -- 
      DROP TABLE IF EXISTS room;
      CREATE TABLE IF NOT EXISTS room (
        id int(11) NOT NULL auto_increment,
        bookdate date NOT NULL default '0000-00-00',
        enddate date NOT NULL default '0000-00-00',
        rate DECIMAL(5,2) NOT NULL,   -- rate, as it could be negociated each booking
        PRIMARY KEY  (id)
      ) TYPE=MyISAM AUTO_INCREMENT=4 ;
      -- Dumping data for table `room`
      -- 
      INSERT INTO room (id, bookdate, enddate, rate) VALUES (NULL, '2005-02-05', '2005-02-06', 40.0);
      INSERT INTO room (id, bookdate, enddate, rate) VALUES (NULL, '2005-02-05', '2005-02-07', 38.00);
      INSERT INTO room (id, bookdate, enddate, rate) VALUES (NULL, '2005-02-05', '2005-02-08', 42.00);
    Then I make everyting in PHP
    PHP Code:
    // your start and end dates
    $datein "2005-02-05";
    $dateout "2005-02-08";

    /* Connecting, selecting database */
    $link mysql_connect("localhost""hotel_user""hotel_passwd")
        or die(
    "Could not connect : " mysql_error());

    mysql_select_db("hotel") or die("Could not select database");

    /* Assume $datein before $dateout */
    $query "SELECT bookdate, enddate, rate FROM room WHERE
     (bookdate <= '
    {$dateout}') AND (enddate >= '{$datein}')";
    echo 
    $query;
    $result mysql_query($query) or die("Query room failed : " mysql_error());
    // make a blank table
    for ($date $datein$date <= $dateout;
            
    $arr explode('-'$date), // yes it's some bloody increment
            
    $date date ('Y-m-d'mktime(000$arr[1], $arr[2]+1$arr[0]) ) )
    {
        
    $cnt[$date] =$amo[$date] = 0;
    }
    // add the days for each booking
    while ($line mysql_fetch_row($result) ) {
        list(
    $bookdate$enddate$rate) = $line;
        
    $start = ($bookdate $datein) ? $datein $bookdate;
        
    $end = ($enddate $dateout) ? $dateout $enddate;
        for (
    $date $start$date <= $end;
                
    $arr explode('-'$date), // yes it's some bloody increment again
                
    $date date ('Y-m-d'mktime(000$arr[1], $arr[2]+1$arr[0]) ) )
        {
            
    $cnt[$date]++;
            
    $amo[$date]+= $rate;
        }
    }
    print_r($cnt);
    print_r($amo);

    /* Free resultset */
    mysql_free_result($result);

    /* Closing connection */
    mysql_close($link); 
    I would think it's no performance problem for normal-sized hotels.
    OK it's quite no SQL but only PHP, would really like to compare (bench) both solutions.
    Not full tested out.
    Bye.
    bertrand Gugger toggg.com linux, PHP, Auvergne/France open source

  9. #34
    SitePoint Zealot
    Join Date
    Oct 2004
    Location
    naperville
    Posts
    189
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    WIthout benching, the SQL will be considerably faster - take advantage of it sprocessing abilities and SQL cache. You have nested loops; this will not be faster then one query.

  10. #35
    SitePoint Enthusiast
    Join Date
    Jan 2005
    Location
    London UK, Tokyo, Japan
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up

    toggg, your contribution is highly appericated...it maybe slower and resource intensive but it shows another way of doing it which will provide a better understanding of PHP. (Merci beacoup)

    now i just have to integrate this(swdev and r937) with my availibilty script! if i have no luck then im off to learn coldfusion on r937's take!

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

    What is fast, where, when ..

    jenny kaur: this PHP is quite simple ... understand it before try ColdFusion (I don't even know what that is...I must take a look)
    Indeed it's whole tested with a real 'hotel' database, 'hotel_user', 'hotel_password',
    constat die() thru errors
    within real mysql-3.23.58-9.1 / php-4.3.10-2.4 / kernel-2.6.10-1.11_FC2
    r937: I love your trick with this permanent 'integer' table, just I will use it making a 'date' (not permanent) trick table
    swdev: I picked up and there in your script. I mean by 100,000 booking a month that should be this way, more or less
    Super Phil: then do Bench ! 100,000 booking ? What is performance? In which human/machine/system will that act ?
    Anyway, you're right, should not nest SQL in loops, better query everything in once. Perhaps php/mysql_php_functions do that allready ? smbdy knows ?

    It's lovely exercises.
    (Merci beaUcoup)
    bertrand Gugger toggg.com linux, PHP, Auvergne/France open source

  12. #37
    SitePoint Enthusiast
    Join Date
    Jan 2005
    Location
    London UK, Tokyo, Japan
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs down

    ok i am okay with the room count but cant get the availibilty correct! :'(

    here is my DB:

    +---+--------------+--------------+---------------+
    | ID | room_number | .. bookdate .. | .... enddate ... |
    +---+--------------+--------------+---------------+
    | 1 .| _____ 1 _____| .. 2005-02-02 | .. 2005-02-03 |
    +---+--------------+--------------+---------------+
    | 2 .| _____ 2 _____| .. 2005-02-02 | .. 2005-02-04 |
    +---+--------------+--------------+---------------+
    | 3 .| _____ 2 _____| .. 2005-02-01 | .. 2005-02-02 |
    +---+--------------+--------------+---------------+
    this is my script to check for available rooms:

    PHP Code:
    <?php

    // desired_start_date dates POST by html Form
    $sy=2005;
    $sm=02;
    $sd=02;
    $sdfull="$sy-$sm-$sd";
    $desired_start_date=date("Y-m-d",strtotime($sdfull));
    // desired_end_date dates POST by html Form
    $ey=2005;
    $em=02;
    $ed=5;
    $ed2=$ed-1;
    $edfull="$ey-$em-$ed2";
    $desired_end_date=date("Y-m-d",strtotime($edfull));

    // Declare functions here
    function my_timestamp($some_date) {
           
    // Null all variables for safety
           
    $result null;
           
    $date explode('-'$some_date);
           
    $some_date_year $date[0];
           
    $some_date_month $date[1];
           
    $some_date_day $date[2];
           
    $result mktime(000$some_date_month$some_date_day$some_date_year);
           return 
    $result;
    }
    // Declare variables here
    $room 1;
    $maxroom 10;
    $desired_start_date2 $desired_start_date;

    // Now convert desired start and end dates to timestamps
    $desired_start_date_ts my_timestamp($desired_start_date);
    $desired_end_date_ts my_timestamp($desired_end_date);
    $days = ($desired_end_date_ts $desired_start_date_ts) / 86400;


    // Make sure that the beginning is actually before the end
    if ($desired_end_date_ts <= $desired_start_date_ts) {
           echo 
    "You cannot end before you begin.";
           exit;  
    // go back and get valid dates
    }

    //Table to show days between dates
    $s=mktime(0,0,0,$sm$sd$sy); 
    $e=mktime(0,0,0,$em$ed2$ey); 
    echo
    '<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="100%" id="AutoNumber1">';
    echo
    "<tr><td bgcolor=006699 align=center><font color=yellow>Room #</td>";
    while(
    $s<=$e){
        echo
    "<td bgcolor=006699><font color=white><center>";
    echo 
    date('j M',$s)." ";  
    echo
    "</td>";
    $s=$s+86400//increment date by 86400 seconds(1 day) 
    }
    echo 
    "</tr>";


    // Create date array
    for ($i 0$i <= $days$i++) {
           
    $dates[$i] = $desired_start_date2;
           
    $desired_start_date2++;
    }

    /*
      Connect to the database. 
    */
    include ("config.php");

     
    $conn mysql_connect(localhost,$user,$password);
     
    mysql_select_db($database) or die( "Unable to select database");

    // Create the sql statement, uncomment below and comment/remove the $query
     
    $sql "SELECT * from room ORDER by bookdate";


    while (
    $room <= $maxroom) {
           
    // Create the sql statement, uncomment below and comment/remove the $query
            
    $sql "SELECT * from room WHERE room_number = $room";

            
    $result mysql_query($sql$conn) or die("Problem here.");
            
    $count mysql_num_rows($result);
           
          while (
    $row mysql_fetch_array($result)) {

             
           
                   
    $string "<tr><td bgcolor=006699 align=center><font color=yellow>$room</td>";
                   
    // Get data from current row
                   
    $room_num $row['room_number'];
                   
    $room_start_date $row['bookdate'];
                   
    $room_end_date $row['enddate'];

                   
    // Create timestamps for room_start_date and room_end_date
                   
    $room_start_date_ts my_timestamp($room_start_date);
                   
    $room_end_date_ts my_timestamp($room_end_date);

                   
    // Adjust timestamps for check in/out times
                   
    $room_start_date_ts -= 3600;
                   
    $room_end_date_ts -= 3601;

                   
    // Loop dates over count
                   
    foreach ($dates as $curr_date) {
                           
    $cts my_timestamp($curr_date);
                           if (
    $cts >= $room_start_date_ts && $cts $room_end_date_ts) {
                                   
    $string .= "<td bgcolor=red align=center>Booked</td>";
                           } else if (
    $cts $room_start_date_ts || $cts $room_end_date_ts) {
                                   
    $string .= "<td bgcolor=green align=center>Free</td>";
                           } else {
                                   
    $string .= "<td align=center>&nbsp;</td>";
                           }
                   } 
    // end foreach dates
                   
    echo "$string</tr>";
                   
    $string null;
           } 
    // end while fetch_array

           // Increment $room so we don't hang forever
           
    $room++;
    // end while room count
    this is the output:


    [Room #] [___2 Feb___] [___3 Feb___] [___4 Feb___]
    .... 1 .... .... Booked .... ..... Free ...... ..... Free .....
    .... 2 .... .... Booked .... .... Booked .... ..... Free .....
    .... 2 .... ...... Free ...... ..... Free ...... ..... Free .....
    as you can see room 2 is showing as free and booked on the same day...cant see where in my code i have gone wrong!

    oh and i am new to php so my code may be VERY inefficient, r937 can you do another magic sql query and swdev, toggg can u do your PHP magic?

    please help!

  13. #38
    SitePoint Addict toggg's Avatar
    Join Date
    Jan 2005
    Location
    Auvergne/France
    Posts
    253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Excuse me, it's all up and down there.
    1) every variavle you declare in main's scope is globla no matter where it is
    just if it's not whitin a function or a class.
    2) I think a timestamp counts seconds so ++ makes only one second more
    3) Try not to mix HTML which is immediatly output and PHP which can prepare the variables to put in before. (smarty is smart)
    I cannot do more as this code I proposed you, read it, make it run, it actually does ! Then we could see if some points (as rates) are to be adapted.
    Ok my for increments are ugly but they do + 1 day
    Bon courage !
    bertrand Gugger toggg.com linux, PHP, Auvergne/France open source

  14. #39
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937 Thanks for the kind words

    jenny kaur
    Not quite sure I understand what you are after, but I have written this code that gives you a list of avaialbed rooms for a given period. It is heavily based on the original query supplied by r937, with some minor modifications.

    Here is my test data
    Code:
       --
       -- Table structure for table `room`
       --
       
       CREATE TABLE room (
         id int(11) NOT NULL auto_increment,
         room_number int(11) NOT NULL default '0',
         bookdate date NOT NULL default '0000-00-00',
         enddate date NOT NULL default '0000-00-00',
         PRIMARY KEY  (id)
       ) TYPE=MyISAM;
       
       --
       -- Dumping data for table `room`
       --
       
       INSERT INTO room VALUES (1,1,'2005-02-05','2005-02-06');
       INSERT INTO room VALUES (2,2,'2005-02-05','2005-02-07');
       INSERT INTO room VALUES (3,3,'2005-02-05','2005-02-08');
       INSERT INTO room VALUES (4,1,'2005-02-08','2005-02-09');
    and the code
    PHP Code:
       function GetBookedRooms($StartDate$EndDate,  &$Booked)
       {
         
    $sql 'SELECT'
              
    ' DATE_ADD(\'' $StartDate '\', interval i day) as thedate'
              
    ', room_number'
              
    ' FROM'
              
    ' integers'
              
    ' INNER'
              
    ' JOIN room'
              
    ' ON '
              
    ' DATE_ADD(\'' $StartDate '\', interval i day)'
              
    ' BETWEEN room.bookdate AND DATE_ADD(room.enddate, interval -1 day)'
              
    ' WHERE'
              
    ' i'
              
    ' BETWEEN 0 AND TO_DAYS(\'' $EndDate '\') - TO_DAYS(\'' $StartDate '\')'
              
    ' ORDER'
              
    ' BY thedate, room_number'
            
    ;
       
       
    /* ***** START DIAGNOSTIC **** */
       // display sql
       
    echo '<pre>';
       
    print_r($sql);
       echo 
    '</pre>';
       
    /* ***** END DIAGNOSTIC **** */
       
         
    $result mysql_query($sql) or die ('Failed to execute ' $sql .  ' due to ' mysql_error());
         
    // read booked room data
         
    while ($row mysql_fetch_assoc($result))
         {
           
    // store room booked data in array
           // 1st dimension = date room was booked
           // 2nd dimension = room number
           
    $Booked[$row['thedate']][$row['room_number']] = true;
         }
         
    mysql_free_result($result);
       }
       
       
       function 
    GetAvailableRooms($StartDate$EndDate$Booked, &$Available)
       {
         
    // convert all date/time strind to unix timestamps
         
    $sd strtotime($StartDate);
         
    $ed strtotime($EndDate);
       
         
    // loop throught all booked room data
         
    foreach ($Booked as $booked_date => $booked_rooms)
         {
           
    $bd strtotime($booked_date);
       
           
    // if any rooms are booked on a date between the
           // requested start and end dates
           
    if ( ($sd <= $bd) && ($bd <= $ed) )
           {
             
    // for every room that is booked on this date
             
    foreach($booked_rooms as $room_number => $room_booked)
             {
               
    // remove it from the list of available rooms
               
    unset($Available[$room_number]);
             }
           }
         }
       }
       
       
    mysql_connect('localhost''*''**');
       
    mysql_select_db('****');
       
       
    // your start and end dates
       // used to get room booked information
       
    $datein "2005-02-04";
       
    $dateout "2005-02-09";
       
       
    // multi-dimensional array used to hole
       // list of dates and rooms booked
       
    $booked = array();
       
    GetBookedRooms($datein$dateout$booked);
       
       
    /* ***** START OF TEST DATA ***** */
       // build an array of rooms to check availability
       
    for ($i 1$i 5$i++)
       {
         
    $all_room_numbers[$i] = 'Available';
       }
       
    /* ***** END OF TEST DATA ***** */
       
       // start and end date to check availability for
       // probably better to use $datein and $dateout defined above
       
    $start_date '2005-02-08';
       
    $end_date '2005-02-09';
       
    GetAvailableRooms($start_date$end_date$booked$all_room_numbers);
       
       
    // print list of available rooms
       
    echo '<br>=====Rooms Available Between ' $start_date ' and ' $end_date ' =====<br>';
       foreach (
    $all_room_numbers as $room_number => $room_state)
       {
         echo 
    $room_number '<br>';
       } 
    and and example result

    HTML Code:
       =====Rooms Available Between 2005-02-08 and 2005-02-09 =====
       2
       3
       4
    Hope this is what you wanted, or at least, a pointer in the right direction.
    Last edited by swdev; Jan 31, 2005 at 18:13.

  15. #40
    SitePoint Enthusiast
    Join Date
    Jan 2005
    Location
    London UK, Tokyo, Japan
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    swdev, nice to have u back in my thread!

    i am testing your code and i get just room numbers

    =====Rooms Available Between 2005-02-02 and 2005-02-05 =====
    1
    2
    3
    4
    did you run my code that i posted, if i can get the HTML output of that script using yours i will be very grateful...im stuck as your new script does not tell me if room is booked or free.

  16. #41
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oops - my bad didn't read your requirements carefully enough.

    Here is my next attempt

    PHP Code:
        function GetBookedRooms($StartDate$EndDate,  &$RoomStatus)
        {
          
    $sql 'SELECT'
               
    ' DATE_ADD(\'' $StartDate '\', interval i day) as thedate'
               
    ', room_number'
               
    ' FROM'
               
    ' integers'
               
    ' INNER'
               
    ' JOIN room'
               
    ' ON '
               
    ' DATE_ADD(\'' $StartDate '\', interval i day)'
               
    ' BETWEEN room.bookdate AND DATE_ADD(room.enddate, interval -1 day)'
               
    ' WHERE'
               
    ' i'
               
    ' BETWEEN 0 AND TO_DAYS(\'' $EndDate '\') - TO_DAYS(\'' $StartDate '\')'
               
    ' ORDER'
               
    ' BY thedate, room_number'
             
    ;
        
        
    /* ***** START DIAGNOSTIC **** */
        // display sql
        
    echo '<pre>';
        
    print_r($sql);
        echo 
    '</pre>';
        
    /* ***** END DIAGNOSTIC **** */
          
          
    $result mysql_query($sql) or die ('Failed to execute ' $sql .  ' due to ' mysql_error());
          
    // read booked room data
          
    while ($row mysql_fetch_assoc($result))
          {
            
    // store room booked data in array
            // 1st dimension = date room was booked
            // 2nd dimension = room number
            
    $RoomStatus[$row['room_number']][$row['thedate']] = 'Booked';
          }
          
    mysql_free_result($result);
        }
        
        function 
    SetRoomsAvailable($StartDate$EndDate, &$RoomStatus)
        {
          
    // convert all date/time strind to unix timestamps
          
    $sd strtotime($StartDate);
          
    $ed strtotime($EndDate);
        
          foreach(
    $RoomStatus as $room_number => $dates)
          {
            
    $cd $sd;
            while (
    $cd $ed)
            {
              
    $RoomStatus[$room_number][strftime('%Y-%m-%d'$cd)] = 'Free';
              
    $cd strtotime('+1 day'$cd);
            }
          }
        }
          
        
    mysql_connect('localhost''*''**');
        
    mysql_select_db('***');
          
          
        
    /* ***** START OF TEST DATA ***** */
        // build an array of rooms to check availability
        
    for ($i 1$i 5$i++)
        {
          
    $room_status[$i] = array();
        }
        
    /* ***** END OF TEST DATA ***** */
        
        // multi-dimensional array used to hole
        // list of rooms and dates
        
    $booked = array();
        
        
    // start and end date used for status
        
    $start_date '2005-02-04';
        
    $end_date '2005-02-09';
        
    SetRoomsAvailable($start_date$end_date$room_status);
        
    GetBookedRooms($start_date$end_date$room_status);
        
     
    // build display table
        
    $tbl '<table border="1">';
        
        
    // build table header - use dates that are in the array
        
    $tbl .= '<tr>';
        
    $tbl .= '<td>Room Number</td>';
        
    // we use an offset of 1 here, as that is our first room number
        
    foreach ($room_status[1] as $date => $status)
        {
          
    $tbl .= '<td>' $date '</td>';
        }
        
    $tbl .= '</tr>';
        
        
    reset ($room_status);
        
    // for each of our rooms
        
    foreach ($room_status as $room_number => $room)
        {
          
    // create a new row and display the room number
          
    $tbl .= '<tr>';
          
    $tbl .= '<td>'$room_number '</td>';
        
          
    // for each date in out selected range
          
    foreach ($room as $key => $value)
          {
            
    // display the status of this room (booked or free)
            
    $tbl .= '<td>'$value '</td>';
          }
        
          
    // end the row
          
    $tbl .= '</tr>';
        }
        
        
    // close the table
        
    $tbl .= '</table>';
        
        echo 
    $tbl
    This produces this output

    HTML Code:
     SELECT DATE_ADD('2005-02-04', interval i day) as thedate, room_number FROM integers INNER JOIN room ON DATE_ADD('2005-02-04', interval i day) BETWEEN room.bookdate AND DATE_ADD(room.enddate, interval -1 day) WHERE i BETWEEN 0 AND TO_DAYS('2005-02-09') - TO_DAYS('2005-02-04') ORDER BY thedate, room_number
       
     Room Number	2005-02-04 2005-02-05	2005-02-06 2005-02-07	2005-02-08
     1	Free	Booked	Free	Free	Booked
     2	Free	Booked	Booked	Free	Free
     3	Free	Booked	Booked	Booked	Free
     4	Free	Free	Free	Free	Free

  17. #42
    SitePoint Enthusiast
    Join Date
    Jan 2005
    Location
    London UK, Tokyo, Japan
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    cheers you deserve a medal

    swdev, you are the site point guru. ..my code looks so weak compared to this...works perfectly but i tried to change the date output by
    PHP Code:
     foreach ($room_status[1] as $date => $status)
        {
          
    $tbl .= '<td>' $date '</td>';
        }
        
    $tbl .= '</tr>';


    date("j-m"$date); 
    i get:
    70-01-01

  18. #43
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Awwwwwwwwwwww shucks

    You were nearly there with your date function

    replace this line of code
    PHP Code:
     $tbl .= '<td>' $date '</td>'
    with this line
    PHP Code:
     $tbl .= '<td>' date('j-m'strtotime($date)) . '</td>'
    The reason you need to call strtotime is that the date is held in the array as a string in the YYYY-MM-DD format. Strtotime converts this to a UNIX timestamp which is what date required.
    I use the YYYY-MM-DD format as that is what is returned from the query.
    I could have change the query to return the format you wanted, but I prefer to do all date calculations in YYYY-MM-Dd fomat and then just adjust the display are required.

  19. #44
    SitePoint Enthusiast
    Join Date
    Jan 2005
    Location
    London UK, Tokyo, Japan
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ahhhh, yeah.... hey swdev, i did actually do that 3 days ago and it is in my original code...i did it from the PHP manual and i feel like a dork now as i couldnt even remember it....i'm a doofis!

  20. #45
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey - we all have days when we can't get the basics right. Sometimes I spend hours looking a code tearing my hair out. Then I leave it for a couple of hours, comes back to it and usually spot the error within minutes. Thats the beauty of the coding game


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
  •