SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 29
  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,895
    Mentioned
    138 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,016
    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,895
    Mentioned
    138 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,016
    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,895
    Mentioned
    138 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,016
    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"

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    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"

  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,016
    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,016
    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,016
    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 Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    445
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes that is correct: "departures for the user-entered date and up to 7 days thereafter"

    This is exactly what I am trying to achieve.

    now I need to format the $dep variable into yyyy-mm-dd. not sure how as it is retrieved from the form the user submit as: $dep= $_GET["departuredate"] and the date can be printed correctly as dd-mm-yyyy but obviously need to be converted to the other format yyyy-mm-dd


    Before I test the interval part , I want to check that the first part of the query works as shown below:

    WHERE departureDate >= DATE_FORMAT( $dep, '%Y/%m/%d')

    unfortunately, this query displays all entries regardless of the date the user selects. As you can see I tried to format the $dep to the yyyy-mm-dd in the where clause so it can query the mysql table, but it doesnt work. it just displays all entries and dispaly them as yyyy-mm-dd.

    the second task would be to convert the date back to dd-mm-yyyy so it can be displayed in the UK format.


    once this works then I ll try the interval part of the query
    Am i missing something?

  19. #19
    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.

  20. #20
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by hm9 View Post
    WHERE departureDate >= DATE_FORMAT( $dep, '&#37;Y/%m/%d')

    unfortunately, this query displays all entries regardless of the date the user selects.
    ur doing it wrong

    do yourself a favour and echo the sql string instead of passing it to mysql from php, copy it out, then run it in a mysql client

    i think you will discover that $dep without quotes will always yield a zero date, and therefore all departure dates are returned
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  21. #21
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    445
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok. As you suggested, I got it to work first with just putting the date as shown below

    $result = mysql_query("SELECT destination, departurepoint, departureDate, from entries WHERE departureDate >= '2010-05-02' AND departureDate < '2010-05-02' + INTERVAL '7' DAY");

    I know that the query works now, but I need to substitute the date with the variable '$dep' as the date is stored in dd-mm-yyyy format, but not sure how to do it? also need to display it to the user in the uk format

    can you please advise how this can be achieved


    thanks again

  22. #22
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,895
    Mentioned
    138 Post(s)
    Tagged
    2 Thread(s)
    What I would do is rewrite the $dep to yyyy-mm-dd format in PHP.

    Use the following
    PHP Code:
    list($day,$month,$year)=explode('-'$dep);
    $depMysql=$year.'-'.$month.'-'.$day
    Now you can use $depMysql in your query, which has the correct format.

    To give the UK date back to the user, rewrite your query as follows:

    Code SQL:
    SELECT destination, departurepoint, DATE_FORMAT("%d-%m-%Y", departureDate) AS departureDateUK, FROM entries WHERE departureDate >= '2010-05-02' AND departureDate < '2010-05-02' + INTERVAL '7' DAY

    Note that you can't do AS departureDate, because then MySQL will use the rewritten departureDate in your WHERE clause, rendering the query useless.

    So, to sum up:
    PHP Code:
    list($day,$month,$year)=explode('-'$dep);
    $depMysql=mysql_real_escape_string($year.'-'.$month.'-'.$day);
    $result mysql_query("SELECT destination, departurepoint, departureDate, from entries WHERE departureDate >= '".$depMysql."' AND departureDate < '".$depMysql."' + INTERVAL '7' DAY"); 
    I added the mysql_real_escape_string() here to prevent SQL Injection.

    Key thing to note here:
    - Keep every value that MySQL uses in the format MySQL expects (e.g. don't use DATE_FORMAT() in the WHERE clause)
    - Only use functions like DATE_FORMAT() in the SELECT clause if you want to output data to users in another format than MySQL gives it to you

  23. #23
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    445
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks. I got it work by slightly modifying your code as i had a few problems with it but this works now apart from one thing:

    list($day, $month, $year) = split( '[/.-]', $dep);
    $dep3 = "$year-$month-$day<br />\n";
    echo $dep3 ;// to test it works


    $result = mysql_query("SELECT destination,departureDate, DATE_FORMAT('%d-%m-%Y', departureDate) as departureDateUK from entries WHERE departureDate >= '$dep3' and departureDate <= '$dep3' + INTERVAL '7' DAY
    ");

    The only problem left is displaying it to the user. It shows the correct entries but the departureDate field is not displayed so cant see the dates. If i print departureDateUK, it shows nothing in the field. if i print departureDate, it shows the date in the mysql format yyyy-mm-dd.


    print "<td>" . $package["destination"] . "</td>" // this is what i use to print the destination and it works
    ;
    print "<td>" . $package["departureDateUK"] . "</td>" // this si what i use to print the date but it does not show the date
    ;


    Can you spot where the problem is ?is it to do with the printing bit or formatting part? the latter looks ok to me.

  24. #24
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,895
    Mentioned
    138 Post(s)
    Tagged
    2 Thread(s)
    1) $dep3 = "$year-$month-$day<br />\n";

    Remove the "<br />\n", MySQL doesn't know what that would mean.

    2) Make the SQL as follows
    Code SQL:
    SELECT
      destination,DATE_FORMAT(departureDate, '%d-%m-%Y')
    FROM
      entries
    WHERE
      departureDate >= '$dep3' AND departureDate <= '$dep3' + INTERVAL '7'

    i.e.,
    2a) The parameters for DATE_FORMAT are the other way around, so (date, format) instead of (format, date) <-- my bad
    2b) don't select both departureDate as well as DATE_FORMAT(departureDate, '%d-%m-%Y'), since you're never using departureDate (but departureDateUK), this makes query slightly confusing (at least for me ).

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

    Unhappy

    Ok I removed the <br> and tried it as you suggested:

    $result = mysql_query("SELECT destination, DATE_FORMAT(departureDate, '%d/%m/%Y') FROM entires WHERE departureDate >= '$dep3' AND departureDate <= '$dep3' + INTERVAL '7'
    ");


    It doesnt like it. It doesnt return anyhting


    when I echo '$dep3' It prints the date in the mysql format as expected yyyy-mm-dd but It doesnt seem to like the format part. does the format part needs AS after it?


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
  •