MySQL Date Syntax Error

Databases
1

Hi!

I am trying to retrieve a date variable out of my MySQL database through a select-list form, but I keep getting this 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 '\‘2012-08-02\’ AND \‘2013-12-31\’ ORDER BY project_descriptions.proj_date_final ’ at line 1”

Here is the relevant part of the query:

“WHERE project_descriptions.proj_date_final BETWEEN $value”

Here is the variable:

//Grabs the date from the dropdown list
$value=“‘2007-01-01’ AND ‘2013-12-31’”;
if(isset($_POST[‘value’])) {
$value=$_POST[‘value’];
}

Here is the 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>

Essentially, the default value of $value loads just fine when the page renders, but whenever I submit the form (including the All Dates value, which is the same as the default one), I get this error message.

I echoed the query in hopes that I could find something, but it’s all good except the (\') marks. Only when I mess with the quote marks does anything change–usually for the much worse! So I am pretty sure that it is the single quote marks in the value attribute of the option tag is the snag. I tried escaping them (is that the right term?) in the attribute (e.g., value=“\‘2007-01-01\’ AND \‘2013-12-31\’”>) but, again, that just made matters worse.

What am I missing here? Is there a better way to give me a range of dates from my database?

2

… but forgot to show it to us :slight_smile:

3

That is because I set up the the query echo in another page that contains the cascading set of form elements. It printed out the query as the last “else” of the script. When I add it to the page of this single form element, all it prints is the name of the query ($query_projTbl.) Here is what I put:

//Grabs the date from the dropdown list
$value=“‘2007-01-01’ AND ‘2013-12-31’”;
if(isset($_POST[‘value’])) {
$value=$_POST[‘value’];
echo ‘$query_projTbl’ . ‘<br><br>’;
}

However, I already used it to isolate the trouble: the single quote marks in the value attribute. Are you saying that the code I supplied above should work? The weird thing is that it did work, and then stopped.

If it would help, please advise on what to place (and where.) If there is anything else that would help get to the bottom of this, please let me know.

4

what you want to end up with, of course, is…


WHERE project_descriptions.proj_date_final 
   BETWEEN '2007-01-01' AND '2013-12-31'

how you achieve that isn’t really a mysql topic, but rather php

mysql does not need (nor want) those quotes escaped

5

The query does say that when the page loads, but it doesn’t when the form data gets posted. The query is saying:

WHERE project_descriptions.proj_date_final BETWEEN '\‘2007-01-01\’ AND \‘2013-12-31\’

Where did those slashes come from? PHP didn’t put them there, because they don’t show up in any other variables.

And if that isn’t really a MySQL topic, then what would be the proper query to use in this circumstance?

6

i have no idea, but i’m willing to bet that it actually was php

the proper query is in post #4

perhaps you should ask a moderator (hit the little red flag icon) to move this thread to the php forum