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

[TMacFarlane]

Hi!

I referred to this forum thread when trying to build a MySQL search query for my site:

http://www.daniweb.com/web-development/databases/mysql/threads/53025/mysql-select-rows-in-a-date-range

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?

[pritaeas]

What type is column proj_date_final?

[smantscheff]

Your query contains the string "\'". The backslash is not recognized as an escape character by MySQL. Get rid of it. Where does it get into your code?

[TMacFarlane]

The column "proj_date_final" is a date type.

our query contains the string "\'". The backslash is not recognized as an escape character by MySQL. Get rid of it. Where does it get into your code?

This, I think is the key. I do not understand how it got into the query at all! Pretty much all the code is included above. You can see the variable in the option value is exactly the same as it is in the variable definintion, and yet, when it is selected out of the form, it throws in the escape character. How do I stop that from happening?

[pritaeas]

How do I stop that from happening?

Show the code part where you execute your query.

[smantscheff]

How do you submit your query to mysql? Show the PHP code? There must be some escaping done somewhere. Could be that you process the $_POST input with magic_quotes=on (always a bad idea) which would explain the escape character.