#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2011
    Posts
    58
    Rep Power
    4

    Checking for contiguous date ranges from MySQL


    Hi

    I have a table of bookings for properties; columns are propertyID, startDate and endDate.

    I'm displaying the list of properties in a simple table with a short description of their availability status.

    The status types are:

    1) No bookings at all: display 'Now'

    2) Not booked today but has a future booking: display 'Now (has future bookings)'

    3) Booked today and has no further bookings: display the first date after the current booking ends.

    4) Booked today AND has further bookings: display the first date after the last contiguous booking.

    This all works OK, but it's not very elegant. It should also be using mysqli but at the moment my objective is to find a neater approach. The most significant thing is the breakout of the loop, I seem to have forgotten how to do the while( approach, I expect I'll remember in the morning!

    PHP Code:
    $datetoday date('Y-m-d');

    // check if there are current or future bookings for this property
        
    $bookingsq "SELECT *
                        FROM bookings
                       WHERE bookings.propertyID = '
    $propertyID'
                         AND (bookings.startDate >= '
    $datetoday' OR bookings.endDate >= '$datetoday')";
            
        
    $bookingsr mysql_query($bookingsq);
        
    $bookingscount mysql_num_rows($bookingsr);
        
        if (
    $bookingscount == 0){$available 'Now';}  // there are no current or future bookings
        
        
        
    else {  // there are current and/or future bookings for this property

        // see if there's a current booking, and get its end date
        
    $bookingsq "SELECT bookings.endDate AS thisEndDate
                        FROM bookings
                       WHERE bookings.propertyID = '
    $propertyID'
                         AND (bookings.startDate <= '
    $datetoday' AND bookings.endDate >= '$datetoday')";
        
    $bookingsr mysql_query($bookingsq);
        
    $currentbkscount mysql_num_rows($bookingsr);
        
        if (
    $currentbkscount == 0){ // not currently booked

            
    $available "Now (has future bookings)";    
            }
            else{ 
    // currently booked AND has future bookings
                
            // presently we just show the end date of the current booking                
            
    if ($curr mysql_fetch_array($bookingsr)){
                
    extract($curr);
            }

            
    $available date('d.m.y'strtotime($thisEndDate '+ 1 day'));    
            
            
    /*  If a property is currently booked and there ARE future booking(s) - 
             *  show the date after the last date of all the contiguous bookings
             */    

            // we already know the endDate of the present booking ($thisEndDate)
            
            
    $finalEndDate '';
            
    $contq "SELECT bookings.startDate AS checkStartDate
                           , bookings.endDate AS checkEndDate
                        FROM bookings
                       WHERE bookings.propertyID = '
    $propertyID'
                         AND bookings.startDate >= '
    $thisEndDate'
                    ORDER BY bookings.startDate ASC"
    ;
            
    $contr mysql_query($contq)or die('Error getting bookings');
            
            while(list(
    $checkStartDate$checkEndDate) = mysql_fetch_array($contr)){
                
                if (
    $finalEndDate == '' && $checkStartDate == date('Y-m-d'strtotime($thisEndDate '+ 1 day'))){
                    
    // this booking is contiguous from the last!    
                    
    $thisEndDate $checkEndDate;    
                }
                else{ 
    // booking is not contiguous: exit
                    
    $finalEndDate $thisEndDate;    
                }
                
            } 
    // checked all bookings for this propertyID    
                                
                
            
    }
        
        } 
    Thanks in advance for any suggestions.
    Last edited by phpcog2; October 7th, 2013 at 05:16 PM.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2009
    Posts
    694
    Rep Power
    7
    You seem to be doing a TON of stuff that you do not need to be. My simple question is: When the list is built, what order do you wish this to be? It seems you are asking if/then a bit for similar things. As far as start/end dates, are they always the same period length?

    Most people can just ORDER BY startDate/endDate, but it seems you maybe want to list you currently active ones by endDate first, then the future ones by startDate next?
    He who knows not and knows not he knows not: he is a fool - shun him. He who knows not and knows he knows not: he is simple - teach him. He who knows and knows not he knows: he is asleep - wake him. He who knows and knows he knows: he is wise - follow him
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2011
    Posts
    58
    Rep Power
    4
    Thanks for your points. Yes you're right, it is rather longhand.

    When the list is built, it's alphabetical by propertyName (from a different table, who's PK is the propertyID). I don't want to order it by available date - all I need to do is display the availability status according to the 4 rules I've noted.

    The start/end dates are not the same period length; they can range from a single day to an entire year or more.

    The code I have works, it's just not efficient. I expect I could do the whole thing - initial list included - in MySQL but I wanted to neaten this bit first, if possible.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2009
    Posts
    694
    Rep Power
    7
    I guess a quick question... I was tossin sumtin together real quick, and I was wondering if you desired to keep the Unix timestamp for your dates, or would actual dates be ok for you in your database?
    He who knows not and knows not he knows not: he is a fool - shun him. He who knows not and knows he knows not: he is simple - teach him. He who knows and knows not he knows: he is asleep - wake him. He who knows and knows he knows: he is wise - follow him
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2011
    Posts
    58
    Rep Power
    4
    I'm not sure what you mean; the dates are stored in the database as a MySQL DATE column: YYYY-MM-DD - that's where they're coming from.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2009
    Posts
    694
    Rep Power
    7
    Ok, that's excellent. Now, as far as the 'contiguous' part goes, will the next start date be the same as the previous end date? Or will it be the day after?
    He who knows not and knows not he knows not: he is a fool - shun him. He who knows not and knows he knows not: he is simple - teach him. He who knows and knows not he knows: he is asleep - wake him. He who knows and knows he knows: he is wise - follow him
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2009
    Posts
    694
    Rep Power
    7
    Lemme know if it works for ya...
    PHP Code:
    function getStatus($PID) {
      global 
    $link;
      
    $booked 0;
      
    $current = new DateTime('', new DateTimeZone('America/Chicago'));
      
    $statement $link->prepare("SELECT startDate, endDate
                                    FROM bookings
                                    WHERE propertyID = ?
                                    AND endDate >= ?
                                    ORDER BY startDate ASC;"
    );
      
    $statement->bind_param('is'$PID$current);
      
    $statement->execute();
      
    $statement->bind_result($col1,$col2);
      
    $statement->store_result();
      if(
    $statement->num_rows == 0) {
        
    $available "Now"// Your #1 definition
      
    } else {
        while (
    $statement->fetch()) {
          
    $startDate DateTime::createFromFormat('Y-m-d'$col1);
          
    $endDate DateTime::createFromFormat('Y-m-d'$col2);
          if(
    $startDate >= $current && $booked == 0) {
            
    $available "Now (has future bookings)"// Your #2 definition
            
    break 2;
          } elseif(
    $startDate <= $current && $endDate >= $current) {
            
    $booked 1;
            
    $dayAfter $endDate->add(new DateInterval('P1D'));
            
    $available "Next opening: " $endDate->format('m/d/Y'); // Your #3 definition
          
    } elseif($startDate == $dayAfter) {
            
    $dayAfter $endDate->add(new DateInterval('P1D'));
            
    $available "Next opening: " $endDate->format('m-d-Y'); // Your #4 definition
          
    }
        }
      }
      
    $statement->close();
      
    // Then you can echo or return your $available variable here.


    EDIT: Explanation...

    With the 'AND endDate >= ?', the single MySQL query will grab us all booking that are NOT of the past, expired.

    With the num_rows, simply if there is nothing returned, there's no bookings, so we can get that out of the way quick.

    The first thing we check is meerly if something is CURRENTLY booked/active or not.
    If one IS NOT, then state available NOW, and since we have we entered this section due to num_rows != 0, we can also state there are more future bookings.
    If one IS, then we set the $booked variable to 1 and grab its end date + 1 for the available date.

    Then we try the next row returned, and ONLY IF the start date equals the day after the previous end, then we update the available date as we did before.

    If the next row ran does not land on the available date, this means it is not contiguous, and is spaced into the future, so we ignore the rest and keep the last available date we updated to.
    Last edited by Triple_Nothing; October 10th, 2013 at 02:14 PM.
    He who knows not and knows not he knows not: he is a fool - shun him. He who knows not and knows he knows not: he is simple - teach him. He who knows and knows not he knows: he is asleep - wake him. He who knows and knows he knows: he is wise - follow him
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2011
    Posts
    58
    Rep Power
    4
    Thank you - and apologies for the late reply; I haven't had time to log in lately and when I went to start a new thread today about a totally different question, I remembered this one.

    I'll have a test of your suggestion soon as I can (I'm on another project at the moment).

    Thanks again for such a detailed suggestion, very much appreciated.
    Last edited by phpcog2; October 21st, 2013 at 12:10 PM.

IMN logo majestic logo threadwatch logo seochat tools logo