SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot vbthanks's Avatar
    Join Date
    May 2001
    Location
    Sydney
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Date selection (...next 7 days)

    Hello,
    I have 2 'Date' columns in my table called 'startDate', and another called 'endDate'. They both store values like YYYY-MM-DD.
    An event might have a startDate of 2003-09-30 and an endDate of 2003-11-29 for example.

    I'd like to show rows that are valid for the next 7 calendar days. The above date range would show.
    startDate 2003-10-10 and endDate 2003-10-13 would not show. (10 days away - not 7)

    This is my query for 'today'
    PHP Code:
    $mikeTime date("Y-m-d");
    $sql "SELECT * FROM $events_table WHERE endDate >= '$mikeTime' AND startDate <= '$mikeTime'"
    Is this possible and can anyone help?
    "You know what you know - but that's all you know!"

  2. #2
    ********* Member website's Avatar
    Join Date
    Oct 2002
    Location
    Iceland
    Posts
    1,238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can use the TO_DAYS() function in MySQL, that will convert the date column to the number of days since the year 0. Example:
    PHP Code:
    $sql "SELECT * FROM $events_table WHERE endDate >= '$mikeTime' AND startDate <= '$mikeTime' AND TO_DAYS(startDate) <= (TO_DAYS($mikeTime)+7)"
    or something like this, you get the idea right? Not exatly sure what you want though.
    Any questions just reply.
    - website

  3. #3
    SitePoint Zealot vbthanks's Avatar
    Join Date
    May 2001
    Location
    Sydney
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks website, but not exactly sure what you mean either!
    I need to select the rows that are (7 days or less) ahead of today's date, using the 2 columns 'startDate' and 'endDate'.
    Cheers,
    Mike
    "You know what you know - but that's all you know!"

  4. #4
    SitePoint Wizard Lats's Avatar
    Join Date
    Jun 2003
    Location
    Melbourne, AU
    Posts
    1,142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Something like...
    PHP Code:
    $sql "SELECT * FROM $events_table WHERE startDate between now() and date_add(now(), interval 7 days)" 
    Lats...

  5. #5
    ********* Member website's Avatar
    Join Date
    Oct 2002
    Location
    Iceland
    Posts
    1,238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hmm, maybe an improved version of Lats query.
    PHP Code:
    $sql "
    SELECT * FROM 
    $events_table 
     WHERE startDate BETWEEN NOW() AND date_add(now(), INTERVAL 7 days) 
     OR NOW() BETWEEN startDate AND endDate"

    So if I understand correctly, you want to get those events that are running or will start in the next 7 days, right?

    Hope this helps!
    - website

  6. #6
    SitePoint Zealot vbthanks's Avatar
    Join Date
    May 2001
    Location
    Sydney
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    "you want to get those events that are running or will start in the next 7 days, right?" - Correct!
    But I get an "mysql_fetch_array(): supplied argument is not a valid MySQL resource" error with that query?
    Cheers,
    Mike
    "You know what you know - but that's all you know!"

  7. #7
    SitePoint Zealot vbthanks's Avatar
    Join Date
    May 2001
    Location
    Sydney
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Fixed it - needed '7 Day' - not '7 days'.
    Thanks for your help - works beautifully!
    Cheers,
    Mike
    "You know what you know - but that's all you know!"


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
  •