SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2001
    Location
    Helston, Cornwall, England
    Posts
    76
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Between dates select question

    Hi again,

    MySQL ver 4.0.25

    I'm still playing with a rental calendar that I was originally using just to display booked dates etc, then I had the notion that I could use the same calendar to display prices etc. and also add a search to.

    I've looked at a few solutions on this forum, but they only enter booked dates into the table and not those that are available.

    My table is basically this:
    tbl_availability
    ----------------------------------------------
    id(int11) | status(int1) | ddate(date) | price(int4)
    ----------------------------------------------
    45 | 1 | 2006-02-01 | 250
    45 | 1 | 2006-02-02 | 250
    45 | 1 | 2006-02-03 | 250
    45 | 1 | 2006-02-04 | 300
    45 | 5 | 2006-02-05 | 300
    45 | 3 | 2006-02-06 | 300
    45 | 3 | 2006-02-07 | 300
    45 | 5 | 2006-02-08 | 300

    I enter a status code of 3 for booked with a code of 5 for changeover days(a quick fix due to not being able to do subqueries on MySQL ver), status code 1 is available.

    The search uses an $arrival_date and $dep_date and I am stuck on part of the query.

    In long hand I want to be able to select property_id from tbl_availability where ALL ddates between $arrival_date and $dep_date have a value of 1 or 5. Thereby removing any property that has a 3 within those dates from the results. Can this be done without a subquery and if so what logic should I apply?

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    select property_id
         , count(*) - sum(case status when 1 then 1 when 5 then 1 else 0 end) as unavail
      from tbl_availability
     where ddate between '$arrival_date' and '$dep_date'
    group
        by property_id
    having unavail = 0

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2001
    Location
    Helston, Cornwall, England
    Posts
    76
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Longneck, have you time to explain the

    count(*) - sum(case status when 1 then 1 when 5 then 1 else 0 end) as unavail
    I take it this will filter through the results to find dates where the status is unavailable and then the last part of the query removes them from the results?

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    not quite. the where clause selects all of the rows in that date range, regardless of the status. the qhole query is grouped by the property_id. then the count(*)... line counts the number of rows for that property, then i subtract out the number of rows with a status of 1 or 5. finally, the having clause removes all rows where the property has no unavailable dats in that range.

    if that didn't make sense, run this query instead. compare the query to the one i posted above to see what i changed. i think that will help you figure out what's going on:
    Code:
    select property_id
         , count(*) as avail
         , sum(case status when 1 then 1 when 5 then 1 else 0 end) as unavail
      from tbl_availability
     where ddate between '$arrival_date' and '$dep_date'
    group
        by property_id

  5. #5
    SitePoint Enthusiast
    Join Date
    Oct 2001
    Location
    Helston, Cornwall, England
    Posts
    76
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your explanation make total sense, I'm going to sit down and spend some time going over the queries so that I understand it fully. Thanks for your time.

  6. #6
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    wow. i completely misunderstood your explaination. you are 100% correct. the first time i read "then the last part of the query removes them from the results?" i thought you were refering to the sum(case...) part of the line you quoted.

  7. #7
    SitePoint Enthusiast
    Join Date
    Oct 2001
    Location
    Helston, Cornwall, England
    Posts
    76
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I haven't seen a query like this before, had no need for one until now so understanding how it works will help for future projects. Thanks again.


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
  •