SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Member
    Join Date
    Dec 2010
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL Query Date Range Question

    Hi All, I'm new to PHP/MySQL and am stumped with what should be a simple process of posting a date range to a PHP script that will query data for the selected range. The script receives the two dates as confirmed when I echo for the variables. For example:

    <?php echo $_POST ["date3"]; ?> to
    <?php echo $_POST ["date4"];?>

    This properly displays "2011-01-08 to 2011-01-09" when I set those dates in the calendar form and submit. But what I can't figure out is how to create variables for these dates and query the database for the submitted range. If I hard code a date into the query using "WHERE posttime LIKE '%2011-01-08%" the query succeeds and produces expected results:

    $result = mysql_query('SELECT points, service, posttime, network, name, value FROM points, redirects WHERE points.qotdid = redirects.qotdid AND points.posttime LIKE "%2011-01-08%" ORDER BY redirects.network, redirects.name, points.posttime');

    How should the query look if I want to replace the static date with the range passed from the form? I would assume:

    $fromdate = $_POST['date3'];
    $todate = $_POST['date4'];

    $result = mysql_query('SELECT points, service, posttime, network, name, value FROM points, redirects WHERE points.qotdid = redirects.qotdid AND points.posttime BETWEEN $fromdate and $todate ORDER BY redirects.network, redirects.name, points.posttime');

    but this doesn't work....what am I doing wrong?

    Thanks in advance for any help.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    BETWEEN '$fromdate' AND '$todate'

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

  3. #3
    SitePoint Member
    Join Date
    Dec 2010
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry I failed to include the quotes, although I tried that too...it gives me this error:

    Parse error: syntax error, unexpected T_VARIABLE in /Applications/MAMP/htdocs/offers/admin/querystats.php on line 45

    Any idea why? It's almost as if the 'BETWEEN' operator is not recognized - it doesn't change colors in my text editor like 'SELECT', 'FROM', 'WHERE' etc.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    construct your SQL statement, and then, instead of executing it, echo it, then copy/paste it into mysql and see what the actual mysql error is
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Dec 2010
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Do I have to do anything to convert the posted date format to how the time stamps are recorded in the database? For example, the form is using 'YYYY-MM-DD' and the database includes time as in 'YYYY-MM-DD HH:MM:SS'.

    Thanks for the help!

  6. #6
    Unobtrusively zen silver trophybronze trophy
    paul_wilkins's Avatar
    Join Date
    Jan 2007
    Location
    Christchurch, New Zealand
    Posts
    14,716
    Mentioned
    103 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by ba$h View Post
    Sorry I failed to include the quotes, although I tried that too...it gives me this error:

    Parse error: syntax error, unexpected T_VARIABLE in /Applications/MAMP/htdocs/offers/admin/querystats.php on line 45
    Your SQL string is constructed using single quotes, so you will need to use double quotes within it around the date values

    PHP Code:
    $result mysql_query('... BETWEEN "' $fromdate '" and "' $todate '" ORDER BY ...'); 
    When double quotes are used for string delimeter, you'll need to instead use single quotes inside it around the values.

    PHP Code:
    $result mysql_query("... BETWEEN '" $fromdate "' and '" $todate "' ORDER BY ..."); 
    Double quotes also allow you to make use of variable expansion as well.

    PHP Code:
    $result mysql_query("... BETWEEN '$fromdate' and '$todate' ORDER BY ..."); 
    The strings documentation page has full details about all of this, and more.
    Programming Group Advisor
    Reference: JavaScript, Quirksmode Validate: HTML Validation, JSLint
    Car is to Carpet as Java is to JavaScript

  7. #7
    SitePoint Member
    Join Date
    Dec 2010
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Paul! When I tried the first solution I got this error:

    Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING in /Applications/MAMP/htdocs/offers/admin/querystats.php on line 45


    But when I tried the last, it worked!!! Thank you VERY MUCH!

  8. #8
    Unobtrusively zen silver trophybronze trophy
    paul_wilkins's Avatar
    Join Date
    Jan 2007
    Location
    Christchurch, New Zealand
    Posts
    14,716
    Mentioned
    103 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by ba$h View Post
    Thanks Paul! When I tried the first solution I got this error:

    Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING in /Applications/MAMP/htdocs/offers/admin/querystats.php on line 45
    Yes, I spotted that after posting, and fixed it right up.

    Quote Originally Posted by ba$h View Post
    But when I tried the last, it worked!!! Thank you VERY MUCH!
    You're welcome.
    Programming Group Advisor
    Reference: JavaScript, Quirksmode Validate: HTML Validation, JSLint
    Car is to Carpet as Java is to JavaScript


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
  •