SitePoint Sponsor

User Tag List

Results 1 to 20 of 20
  1. #1
    SitePoint Wizard lukeurtnowski's Avatar
    Join Date
    Mar 2003
    Location
    Coronado
    Posts
    1,666
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Changing a query to use BETWEEN?

    I'm trying to convert my SQL query so I can only show the results which are between a from date and a to date?
    Heres the original...

    SELECT Approve, ID, Name
    FROM PROVIDER
    WHERE Approve = '1'
    ORDER BY ID ASC
    LIMIT $from, $max_results

    Heres what I want to do..

    SELECT Approve, ID, Name, From, To
    FROM PROVIDER
    WHERE Availibility BETWEEN From AND To AND Approve = '1'
    ORDER BY ID ASC
    LIMIT $from, $max_results

    Is this ok?
    Thanks....
    "Oh, and Jenkins--apparently your mother died this morning."

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    are From and To the actual column names?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard lukeurtnowski's Avatar
    Join Date
    Mar 2003
    Location
    Coronado
    Posts
    1,666
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Yes, is that ok, or should I have em named something else?
    "Oh, and Jenkins--apparently your mother died this morning."

  4. #4
    SitePoint Enthusiast
    Join Date
    May 2007
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try this query:

    SELECT Approve, ID, Name, `From`, `To`
    FROM PROVIDER
    WHERE Availibility BETWEEN `From` AND `To` AND Approve = '1'
    ORDER BY ID ASC
    LIMIT $from, $max_results
    PHP Functions Class/Php Stuff: php-classes-functions.blogspot.com
    SMS World: sms-treasure.blogspot.com
    Finance Business India: financebusinessindia.blogspot.com
    Retail Market India: retailmarketindia.blogspot.com

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i'm trying to imagine the rows in that table

    let's see, there's a column called Availability, and a column called From, and a column called To

    and on some of those rows, the value of Availability is not betwwen the values From and To


    why?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard lukeurtnowski's Avatar
    Join Date
    Mar 2003
    Location
    Coronado
    Posts
    1,666
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Sorry, im not to good at explaining it.
    Availibility will not be a Column, it will be a date selected on a form and when the date is selected, I want to display the results that fall between that date.
    "Oh, and Jenkins--apparently your mother died this morning."

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    BETWEEN is correct

    what are the datatypes of From and To?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard lukeurtnowski's Avatar
    Join Date
    Mar 2003
    Location
    Coronado
    Posts
    1,666
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    well I haven't declared them yet so what data type do you suggest?

    (datetime)
    "Oh, and Jenkins--apparently your mother died this morning."

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    if they are dates, DATE, but if they are datetimes, then DATETIME

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Wizard lukeurtnowski's Avatar
    Join Date
    Mar 2003
    Location
    Coronado
    Posts
    1,666
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    ok,. here's my query, thank you!
    PHP Code:
    $result mysql_query("
        SELECT Approve, ID, Name 
        FROM PROVIDER 
         WHERE Availibility BETWEEN `From_Date` AND `To_Date` AND Approve = '1'
        ORDER BY ID ASC 
        LIMIT 
    $from$max_results 
        "
    ); 
    How do I
    1) Make Availibility be todays date
    2) Ill have people who aren't visiting so no To_Date or From_Date will be entered so their values would be 0000-00-00, how do I make it so that those people are output from the query?
    Thanks
    "Oh, and Jenkins--apparently your mother died this morning."

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    1) WHERE CURRENT_DATE BETWEEN From_Date AND To_Date AND Approve = '1'

    2) no idea what you mean, sorry
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Wizard lukeurtnowski's Avatar
    Join Date
    Mar 2003
    Location
    Coronado
    Posts
    1,666
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Yea, im bad at explaining it. I have ONLY visitors filling out the To_From and the From_Date fields. So Id like to output always all non-visitors and every visitor only whose From_Date and To_Date is between the the current date.
    Is that any better?
    Thanks
    "Oh, and Jenkins--apparently your mother died this morning."

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    not really

    how do you tell the difference between a visitor and a non-visitor

    i mean, isn't a non-visitor by definition not even on the site?

    and even if you do allow non-visitors to visit your site, how does that affect the SQL?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Wizard lukeurtnowski's Avatar
    Join Date
    Mar 2003
    Location
    Coronado
    Posts
    1,666
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Yea, I dont even know if I'm setting this up right.
    In my table I have a To_Date and From_Date fields which are optional (only to be filled out if your visiting) People that aren't visiting Vegas (like if they live there) dont fill out that part of the form. Now when people want to see who will be available that day, Im trying to query the table so that is shows all people living there and the people visiting Vegas that day.
    Is that ok?
    (How should it be set up?)
    Thanks
    "Oh, and Jenkins--apparently your mother died this morning."

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    sorry, i have no idea what you're doing
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    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)
    make the from_date and to_date field allow nulls. if they don't provide from_date and to_date then set the field to NULL. stick an OR From_Date IS NULL in the WHERE clause.

  17. #17
    SitePoint Wizard lukeurtnowski's Avatar
    Join Date
    Mar 2003
    Location
    Coronado
    Posts
    1,666
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    ok,
    Its basically a web page for scheduling appointments in the Vegas area and some of the people signing up on the site would only be in vegas lets say from
    September 16 to 25 for a convention
    Another person would only be there the 18-22 of September
    Also, lets say someone lives in Vegas.
    So when these three people sign up to say when they will be in Vegas, only the first two people need to fill out the visiting part of the form (have entries in the From_Date, To_Date fields)
    So lets say someone visits the page (w/the guery on the 12th of September only the person living in Vegas would be available.
    If someone visits the page on the 17th, the 1 person visiting then and the one living there would be output.
    If the page were visited the 20th, all three people would show
    If the page were visited on the 25th, the 1 person visiting then and the 1 who lives there would show.
    But if the page were visited the 26th, only the person living there would be available.
    So only the ones visiting would be affected
    ok?
    "Oh, and Jenkins--apparently your mother died this morning."

  18. #18
    SitePoint Wizard lukeurtnowski's Avatar
    Join Date
    Mar 2003
    Location
    Coronado
    Posts
    1,666
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Is this ok
    PHP Code:
    $result mysql_query("
        SELECT Approve, ID, Name 
        FROM PROVIDER 
         WHERE CURRENT_DATE BETWEEN `From_Date` AND `To_Date` OR From_Date IS NULL AND Approve = '1'
        ORDER BY ID ASC 
        LIMIT 
    $from$max_results 
        "
    ); 
    "Oh, and Jenkins--apparently your mother died this morning."

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you probably want this --
    Code:
     WHERE ( 
           CURRENT_DATE BETWEEN From_Date AND To_Date 
        OR From_Date IS NULL 
           ) 
       AND Approve = '1'
    make sure you understand the difference between this and what you had, and why

    p.s. please also stop using those backticks
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    SitePoint Wizard lukeurtnowski's Avatar
    Join Date
    Mar 2003
    Location
    Coronado
    Posts
    1,666
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    ok, thanks...
    "Oh, and Jenkins--apparently your mother died this morning."


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
  •