SELECT rows in a Date Range (Getting syntax errors)

[MadMac10]

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:

WHERE project_descriptions.proj_date_final BETWEEN $value ORDER BY project_descriptions.proj_date_final ASC

$value is extracted from a select menu:

<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:

//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?

[NogDog]

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:

<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).

$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.)

[MadMac10]

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?

[MadMac10]

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.