SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict ozmart2004's Avatar
    Join Date
    Feb 2004
    Location
    Albury, NSW, Australia
    Posts
    215
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL syntax problem

    I have a MySQL table that holds events, some are single day events and some run for a week (for example). I set up a little form so that users could select dates and then the events between those dates would display.

    Code:
    "SELECT id, title, description, startTime, endTime, startDate, endDate FROM mod_calendar_events WHERE startDate >= '$startDate' AND endDate <= '$endDate' ORDER BY startDate";
    The problem is that this SQL is not picking up events that run over a period of day - only the daily events. The statement seems fine... It's odd with date formats in MySQL, the only thing I can think of is a problem with date formats. I found this on the web:
    http://www.techonthenet.com/sql/between.htm

    where they suggest to use syntax like this:


    Code:
    "SELECT id, title, description, startTime, endTime, startDate, endDate 
    FROM mod_calendar_events 
    WHERE startDate >= to_date('$startDate','yyyymmdd')
    AND endDate <= to_date('$endDate','yyyymmdd') ORDER BY startDate";
    However this generates this error:

    You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '('20050220','yyyymmdd') AND endDate <= to_date('20050226','yyyy


    Can anyone tell me where I'm going wrong with this? What is the snytax error and is the SQL logic sound?

    Thks
    Martin

  2. #2
    SitePoint Addict ozmart2004's Avatar
    Join Date
    Feb 2004
    Location
    Albury, NSW, Australia
    Posts
    215
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK I think I was using the wrong function as to_date() seems to be an oracle one so I have tried using str_to_date()

    Code:
    "SELECT id, title, description, startTime, endTime, startDate, endDate FROM mod_calendar_events WHERE startDate >= str_to_date('$startDate','yyyymmdd') AND endDate <= str_to_date('$endDate','yyyymmdd') ORDER BY startDate";
    Unfortunately I still get the same error:

    You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '('20050220','yyyymmdd') AND endDate <= str_to_date('20050226','

    :-(

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,020
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    you don't actually need str_to_date either

    you're probably getting the syntax error because str_to_date is only supported in version 4.1.1 and up

    but like i said, you don't need it

    mysql accepts strings in 'yyyymmdd' format, as well as numbers in yyyymmdd format

    so try

    ... WHERE startDate >= '$startDate' AND endDate <= '$endDate'
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Addict ozmart2004's Avatar
    Join Date
    Feb 2004
    Location
    Albury, NSW, Australia
    Posts
    215
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi. Ok I can ditch the str_to_date function but that lands me back at square one.

    The problem with running events for example is that they may run from Sunday to the next Sunday. If someone does a search between Sunday and Friday, the endDate falls outside the selected date and so the event is not displayed. What I need to do is make sure that all events including events that run over a few days are displayed even if their final end date is outside the selected range.

    Is there a way to do this or is there a better approach??

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,020
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    yes, there is

    best way to understand this is to see a timeline:
    Code:
               $startdate    $enddate
                  |            |     
    1     S====E  |            |     
                  |            |     
    2          S==|=E          |     
                  |            |     
    3          S==|============|=E 
                  |            |     
    4             |   S====E   |     
                  |            |     
    5             |         S==|=E  
                  |            |     
    6             |            |   S====E  
                  |            |
    here you can see several events, marked with S for start and E for end

    the main thing to notice here is that the events which you don't want are events 1 and 6, and these can be characterized as having both start and end either before $startdate or after $enddate -- all other events overlap the range of $startdate to $enddate

    so your query becomes
    Code:
    select ...
      from ...
     where not
           (
           startdate < $startdate
       and enddate   < $startdate
        or startdate > $enddate
       and enddate   > $enddate
           )
    neat, eh?

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

  6. #6
    SitePoint Addict ozmart2004's Avatar
    Join Date
    Feb 2004
    Location
    Albury, NSW, Australia
    Posts
    215
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thats great...I knew there must be a way but don't know quite enough about SQl to be able to see it. Thanks, your a life-saver :-)


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
  •