Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    New Coder
    Join Date
    Jan 2010
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Between two dates

    I have two dates, current one, and the date of expiry(which is 14 days after current day).
    I want all articles between current date and date of expiry to return to my page.
    I have tried <= operator and BETWEEN, however nothing returns to my page.
    below is the code :

    PHP Code:
    $result mysql_query ("SELECT * FROM db WHERE date BETWEEN '$curdate' AND '$expdate'"$db
    What seems to be wrong?

  • #2
    bdl
    bdl is offline
    Regular Coder
    Join Date
    Apr 2007
    Location
    Camarillo, CA US
    Posts
    590
    Thanks
    4
    Thanked 83 Times in 82 Posts
    What value do the variables $curdate and $expdate hold?

    Do they actually have any value at this point in the script?

    Does the SQL statement work in MySQL using valid date values?

    Does the variable $db hold a valid MySQL connection resource?

    Is there an error returned?

    This should answer most of these questions:
    PHP Code:
    $sql"SELECT * FROM db WHERE date BETWEEN '{$curdate}' AND '{$expdate}'";
    echo 
    "<p>SQL statement: {$sql}</p>";
    $result mysql_query ($sql$db) OR die(mysql_error()); 

  • #3
    New Coder
    Join Date
    Jan 2010
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts
    $curdate = 27.02.2010
    $expdate = 13.03.2010

    Yes, in MySQL everything is fine. Gives me back everything between those 2 dates.
    Holds valid, for sure.

    No error, just no results.

    The code you provided gives Resource id #7

  • #4
    bdl
    bdl is offline
    Regular Coder
    Join Date
    Apr 2007
    Location
    Camarillo, CA US
    Posts
    590
    Thanks
    4
    Thanked 83 Times in 82 Posts
    Those aren't valid date values. A valid value would be '2010-02-27'.

    What datatype did you use for your date fields?

    MySQL Reference Manual : Date and Time types

    The code you provided gives Resource id #7
    Where is this shown?

  • #5
    New Coder
    Join Date
    Jan 2010
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I used varchar, because I need data in this format only.

    Resourse id 7 is shown after I echo the result of this :

    $sql= "SELECT * FROM db WHERE date BETWEEN '{$curdate}' AND '{$expdate}'";
    echo "<p>SQL statement: {$sql}</p>";
    $result = mysql_query ($sql, $db) OR die(mysql_error());

  • #6
    bdl
    bdl is offline
    Regular Coder
    Join Date
    Apr 2007
    Location
    Camarillo, CA US
    Posts
    590
    Thanks
    4
    Thanked 83 Times in 82 Posts
    Nothing in what I provided should be giving you "Resource id #7". What is below that? Are you attempting to echo $result?

  • #7
    New Coder
    Join Date
    Jan 2010
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Exactly. I echoed the result and it showed me Resource id 7.

    PS. Strange thing, now I can't get SQL query in Phpmyadmin to work with dates, however it worked just 1 hour ago...

  • #8
    bdl
    bdl is offline
    Regular Coder
    Join Date
    Apr 2007
    Location
    Camarillo, CA US
    Posts
    590
    Thanks
    4
    Thanked 83 Times in 82 Posts
    Quote Originally Posted by RxDx View Post
    Exactly. I echoed the result and it showed me Resource id 7.
    Right. What that's telling you is that the variable $result is a reference to a valid MySQL result resource, that you can further use within the scope of the current MySQL connection to reference back to that specific resultset, and retrieve data from it. If you echo'ed the value of $db, it should also show you a resource number, one that references the currently open connection.

    Please read the PHP manual entry for mysql_query(), the entries for mysql_fetch_assoc(), mysql_result(), and while you're at it, have a look at the Language Reference section on Resource Types.

  • #9
    Super Moderator Inigoesdr's Avatar
    Join Date
    Mar 2007
    Location
    Florida, USA
    Posts
    3,642
    Thanks
    2
    Thanked 405 Times in 397 Posts
    RxDx, if you insist on keeping your VARCHAR date field, you can do something like this:
    PHP Code:
    // example values:
    // $curdate = '27.02.2010';
    // $expdate = '13.03.2010';

    $curdate_valid implode('-'array_reverse(explode('.'$curdate)));
    $expdate_valid implode('-'array_reverse(explode('.'$expdate)));
    $result mysql_query ("SELECT * FROM db WHERE STR_TO_DATE(`date`, '%d.%m.%Y') BETWEEN '{$curdate_valid}' AND '{$expdate_valid}'") or die(mysql_error()); 


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •