SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2008
    Location
    Washington DC
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL Date Syntax Error

    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,328
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by TMacFarlane View Post
    I echoed the query ...
    ... but forgot to show it to us
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2008
    Location
    Washington DC
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,328
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    what you want to end up with, of course, is...
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Jul 2008
    Location
    Washington DC
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,328
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by TMacFarlane View Post
    Where did those slashes come from?
    i have no idea, but i'm willing to bet that it actually was php

    Quote Originally Posted by TMacFarlane View Post
    And if that isn't really a MySQL topic, then what would be the proper query to use in this circumstance?
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Tags for this Thread

Bookmarks

Posting Permissions

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