www.webdeveloper.com
Results 1 to 4 of 4

Thread: SELECT rows in a Date Range (Getting syntax errors)

  1. #1
    Join Date
    May 2010
    Location
    Washington, DC region
    Posts
    6

    SELECT rows in a Date Range (Getting syntax errors)

    Hi!

    I was trying to build a MySQL search query for my site, but I just keep getting this syntax 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 '\'2011-01-01\' AND \'2011-12-31\' ORDER BY project_descriptions.proj_date_final ' at line 1"

    Here is the essential part of the query:

    Code:
    WHERE project_descriptions.proj_date_final BETWEEN $value ORDER BY project_descriptions.proj_date_final ASC
    $value is extracted from a select menu:

    HTML Code:
    <select name="value"> 
      <option value="'2007-01-01' AND '2013-12-31'">All Dates</option>
      <option value="'2007-01-01' AND '2007-12-31'">2007</option>
      <option value="'2008-01-01' AND '2008-12-31'">2008</option>
      <option value="'2009-01-01' AND '2009-12-31'">2009</option>
      <option value="'2010-01-01' AND '2010-12-31'">2010</option>
      <option value="'2011-01-01' AND '2011-12-31'">2011</option>
      <option value="'2007-01-01' AND '2012-08-01'">2012</option>
      <option value="'2012-08-02' AND '2013-12-31'">Ongoing</option>
    <select>
    ... and processed through this code:

    Code:
    //Grabs the date from the dropdown list
    $value="'2007-01-01' AND '2013-12-31'";
    if(isset($_POST['value'])) {
        $value=$_POST['value'];
    }
    The default value of $value prints just fine when the page loads, but crashes when the form posts it. After much torubleshooting, I believe the culprit has something to do with the single quotes in the value attribute of the option tag.

    Does anyone have a idea what could be going wrong? Or does anyone have a better idea for displaying this set of values?

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,362
    If you are either applying mysql_real_escape_string() or similar escaping mechanism (or the dreaded and now-deprecated magic_quotes_gpc setting), then that would cause it to fail.

    I believe a more robust and safer way would be something like:
    HTML Code:
    <option value='2012-01-01|2012-06-30'>01/01/2012 - 06/30/2012</option>
    Then in the form-handler, explode() the value into two variables, and apply them separately to the query (within single quotes).
    PHP Code:
    $dates explode('|'$_POST['dates']);
    $sql sprintf(
      
    "SELECT blah blah blah WHERE date BETWEEN '%s' AND '%s'",
      
    mysql_real_escape_string($dates[0]),
      
    mysql_real_escape_string($dates[1])
    ); 
    (You'd want some validation that the array $dates had 2 elements, and possibly do some validation on the date strings to make sure they make sense.)
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    May 2010
    Location
    Washington, DC region
    Posts
    6
    Thanks for these suggestions. This seems really logical to me. I will work on it and get back to you soon. In the meantime, any other suggestions would be greatly appreciated!

    For instance: has anyone else ever done something similar? What did you do?

  4. #4
    Join Date
    May 2010
    Location
    Washington, DC region
    Posts
    6
    This suggestion worked perfectly! Now I got it fixed. Thanks so very much for your awesome help. I appreciate it much more than I can say.
    Last edited by MadMac10; 08-31-2012 at 09:40 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles