SELECT rows in a Date Range (Getting syntax errors)
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:
$value is extracted from a select menu:
WHERE project_descriptions.proj_date_final BETWEEN $value ORDER BY project_descriptions.proj_date_final ASC
... and processed through this code:
<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>
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.
//Grabs the date from the dropdown list
$value="'2007-01-01' AND '2013-12-31'";
Does anyone have a idea what could be going wrong? Or does anyone have a better idea for displaying this set of values?
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:
Then in the form-handler, explode() the value into two variables, and apply them separately to the query (within single quotes).
<option value='2012-01-01|2012-06-30'>01/01/2012 - 06/30/2012</option>
(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.)
$dates = explode('|', $_POST['dates']);
$sql = sprintf(
"SELECT blah blah blah WHERE date BETWEEN '%s' AND '%s'",
"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
How to Ask Questions the Smart Way
(not affiliated with this site, but well worth reading)
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?
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.
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)