SitePoint Sponsor

User Tag List

Results 1 to 25 of 29

Hybrid View

  1. #1
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    445
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Interval part of MYSQL Query not working

    I have 2 mysql queries on a PHP page:

    The first query just checks the exact match of a departure date and returns the exact results. this works as expected:


    $result = mysql_query("SELECT destination, departureDate
    DATE_FORMAT(departureDate, '%d/%m/%Y') as formattedDate from Entries where DATE_FORMAT(departureDate, '%d/%m/%Y') = '$dep' ");

    The second query will only be excecuted if there are no exact entries, that s why i am using the interval so that it can check alternative dates within 7 days for example, rather than say nothing is found. but at the moment, this returns lot of entries beyond the 7 day interval. It should only display the entries within the interval day set. the query is shown below:


    $result = mysql_query("SELECT destination, departureDate, DATE_FORMAT(Date_Add(departureDate, interval 7 day), '%d/%m/%Y') as formattedDate from Entries");






    any ideas why the interval is not working?


    thanks in advance

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,907
    Mentioned
    139 Post(s)
    Tagged
    2 Thread(s)
    It doesn't work because you're selecting it.
    You should use in in a where, like

    Code SQL:
    SELECT
      destination, departureDate, DATE_FORMAT(departureDate, '%d/%m/%Y') AS formattedDate
    FROM
      Entries
    WHERE
      departureDate > DATE_ADD(CURDATE(), INTERVAL 7 DAY)

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    i find it easier to write the syntax without resorting to the DATE_ADD function

    e.g. WHERE departureDate > CURRENT_DATE + INTERVAL '7' DAY

    (note: i don't normally put quotes around numeric constants, but in this case that's the actual SQL standard, and of course mysql supports it)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,907
    Mentioned
    139 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by r937 View Post
    e.g. WHERE departureDate > CURRENT_DATE + INTERVAL '7' DAY
    Didn't know you could do that. Nice one, much more readable.
    It's also two functions less to worry about whether they were with an underscore or not.
    (DATE_ADD and DATE_SUB, among others, have underscores, DATEDIFF does not, it's like the $needle, $haystack problem in PHP ...)

  5. #5
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    445
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Guys.
    I tried to implement this option but it just returns everything. regardless of what i set in the interval part


    WHERE CURRENT_DATE + INTERVAL '7' DAY

    Note. The variable '$dep' is what is actually date selected by the user from the php form so it should add interval to it.

    The other point is that on mysql query I have formatted the date using DATE_FORMAT(departureDate, '%d/%m/%Y') as formattedDate. Which converts the mysql date fromat from yyyy-mm-dd to dd-mm-yyyy, so may be this needs to be taken into account?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    please show your exact latest query

    i think you might have forgotten something in the WHERE clause
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    445
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok this the full query:


    $result = mysql_query("SELECT destination, departureDate DATE_FORMAT(departureDate, '%d/%m/%Y') as formattedDate from entries WHERE departureDate > CURRENT_DATE + INTERVAL '7' DAY
    ");

    Thanks

  8. #8
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,907
    Mentioned
    139 Post(s)
    Tagged
    2 Thread(s)
    CURRENT_DATE() is a function, you forgot the paretheses.

  9. #9
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    445
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry it s still dsplaying everything.

    Note the departureDate is formatted as formattedDate
    and the date value selected by the user is stored in $dep' varaible

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    CURRENT_DATE() is a function, you forgot the paretheses.
    CURRENT_DATE is a (SQL standard) reserved word, you don't need the parentheses

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

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by hm9 View Post
    Ok this the full query:
    that query will not run, because it contains a syntax error

    did you test it outside of php?

    also, you said "and the date value selected by the user is stored in $dep' varaible"

    but the query does not reference this variable

    so what's really going on here?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    445
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok. If I run this query it works and returns the correct result;


    $result = mysql_query("SELECT destination, departureDate
    DATE_FORMAT(departureDate, '%d/%m/%Y') as formattedDate from Entries where DATE_FORMAT(departureDate, '%d/%m/%Y') = '$dep' ");

    As you can see the variable is contained within the where clause and stores whichever date the user selects. Hence it works = it returns the entries that matches the date selected by the user.


    but If I add interval by using the following query, it does not work obviously because it needs the varaible '$dep' to add interval to it :



    $result = mysql_query("SELECT destination,departureDate, DATE_FORMAT(departureDate, '%d/%m/%Y') as formattedDate from Entries WHERE departureDate > CURRENT_DATE() + INTERVAL '7' DAY
    ");


    but I am not sure how to add variable '$dep' in the where clause ?

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    two problems

    first, by reformatting the departuredate column in the WHERE clause, the optimizer cannot use an index on that column, it must use a table scan, so your query will not scale, it will get slower and slower the more rows you have

    the solution is to use STR_TO_DATE on the '$dep' variable to get it into the correct year-month-day sequence, or better yet, format it correctly using php before calling the query

    the second problem is that you haven't adequately explained which dates you're actually trying to retrieve based on the user-entered date...
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    445
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok I see your point but need help sorting it:

    The date that is stored in the variable '$dep' has this format: dd-mm-yyyy.
    The date in the mysql table is stored in this format: yyyy-mm-dd.

    So need to convert the '$dep' into mysql format in php before querying the database. but then I need it back in the uk format dd-mm-yyyy to display to the user.

    using this method, means i dont have to do the date format conversion on the sql query and the interval will work ?

    Could you please advise on how i need to modify the query


    thanks again

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by hm9 View Post
    using this method, means i dont have to do the date format conversion on the sql query and the interval will work ?
    yes

    but please note you still haven't explained how you want the user-entered date to be compared to the stored date in the table

    the interval examples so far in this thread have used CURRENT_DATE but i'm not sure which interval to construct for the user-entered date
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    445
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    [QUOTE=r937;4530236]yes

    but please note you still haven't explained how you want the user-entered date to be compared to the stored date in the table

    Ok If a user enters a date like 13/03/2010 then I want to return the entries that are available within 7 days interval from that date. for example: entries between 12/03/2010 and 20/03/2010
    so need something like select the entries where the dates in the table are within 7 days interval of the date selected by the user in the form of the variable '$dep'

    does this make sense?

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by hm9 View Post
    does this make sense?
    sort of

    the 12th through the 20th is actually 8 days (or 9, if you count both ends of the range)

    so you want "departures for the user-entered date and up to 7 days thereafter" (this is what i meant by an explanation -- who knows, you could've meant 7 days before, or both 7 days before and 7 days after)

    and i presume today's date is no longer relevant

    Code:
    WHERE departuredate >= '$dep'
      AND departuredate  < '$dep' + INTERVAL '7' DAY
    with '$dep' formatted to year-month-day sequence

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

  18. #18
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,706
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    stop worrying about formatting your date altogether. get your query working correctly first and then add the date_format to the SELECT clause only, not the WHERE part.


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
  •