Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    Regular Coder
    Join Date
    Sep 2007
    Posts
    125
    Thanks
    30
    Thanked 0 Times in 0 Posts

    SQL syntax error with PHP variables

    I'm getting the following 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 'div, writer, sme, progress, status, due) VALUES (Test Project,test description,t' at line 1

    Based on this code:
    Code:
    $insertSQL = sprintf("INSERT INTO projects (proj_name, proj_desc, sponsor, priority, div,
    writer, sme, progress, status, due)
    VALUES (".$_POST['proj_name'].",".$_POST['desc'].",".$_POST['sponsor'].",".$_POST['priority'].
    ",".$_POST['div'].",".$_POST['writer'].",".$_POST['sme'].",".$progress.",'Open',".
    $mysqlFormat.'"');
    Regarding that code, this is the values of the variable noted in there:

    $progress = "Project opened."

    $mysqlFormat = 2007-12-27

    The $mysqlFormat is being put into the "due" column that is listed as a "DATE" type in my table. If you need other info to help troubleshoot, please let me know. Thanks!...

  • #2
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,151
    Thanks
    2
    Thanked 335 Times in 327 Posts
    The error message points to where the query could not be parsed due to a syntax error - the right syntax to use near 'div. Div is a reserved keyword. I recommend renaming that column to avoid problems (it is possible to put references to it in back-ticks ` but this is a mysql specific workaround and will create problems if you ever move to a different database or if mysql switches to more closely follow standards in the future.)
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #3
    Regular Coder
    Join Date
    Sep 2007
    Posts
    125
    Thanks
    30
    Thanked 0 Times in 0 Posts
    Okay - I kept all the code the same, except I renamed "div" to "division" (in both the code and my database table), and now I am 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 'project,test description,test sponsor,Medium,ALL,,,Project opened.,'Open',2007-1' at line 1

    The "writer" and "sme" fields were left blank in my form (so the $_POST variables), which is why there are just blank commas there after "ALL" in the error above, but in my database table setup, I have them set to "NULL", so if they're empty, then doesn't that mean that it is okay to have nothing in them? Or am I wrong about that?

    If I DO fill in the "writer" and "sme" fields, I get 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 'project,test description,test sponsor,Medium,ALL,sample writer,sample sme,Projec' at line 1

    Now what? Thanks for that tip on "div", by the way!!!
    Last edited by ShootingBlanks; 11-13-2007 at 04:49 PM.

  • #4
    Senior Coder
    Join Date
    Jan 2007
    Posts
    1,648
    Thanks
    1
    Thanked 58 Times in 54 Posts
    You need to quote your values, as such:

    Code:
    INSERT INTO table(ID, NAME, ETC) VALUES(NULL, 'This is the name', 'This is etc.')

  • #5
    Regular Coder
    Join Date
    Sep 2007
    Posts
    125
    Thanks
    30
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by aedrin View Post
    You need to quote your values, as such:

    Code:
    INSERT INTO table(ID, NAME, ETC) VALUES(NULL, 'This is the name', 'This is etc.')
    So how does that explain my second error above when I DID fill in all the fields?...

  • #6
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,151
    Thanks
    2
    Thanked 335 Times in 327 Posts
    He did not say to fill them in, he said to put quotes around them. String values in queries must be enclosed in single-quotes, otherwise they are treated as numeric values or as keywords.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #7
    New Coder
    Join Date
    Nov 2007
    Posts
    12
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by ShootingBlanks View Post
    So how does that explain my second error above when I DID fill in all the fields?...
    A sidenote:
    I do not know why you use sprintf() in this case ???
    -------------------
    This format works well for me:
    PHP Code:
    $sql "INSERT INTO $table ( name,time ) VALUES ( '{$persons['name']}','$time' )";
    $db->query($sql); 
    I use only single quotes inside string.
    I escape indexed variables with { ... }

    Which should give this for you. Good luck!
    PHP Code:
    $insertSQL 
    "INSERT INTO projects 
    (proj_name, proj_desc, sponsor, priority, division, writer, sme, progress, status, due)
    VALUES
    ( '{$_POST['proj_name']}','{$_POST['desc']}','{$_POST['sponsor']}','{$_POST['priority']}',
    '{$_POST['div']}','{$_POST['writer']}','{$_POST['sme']}','$progress','Open','$mysqlFormat' )"

    Last edited by phpBuddy; 11-13-2007 at 06:22 PM.
    echo "PHP 5.2.5";
    exit ( "Script End" );

  • #8
    Regular Coder
    Join Date
    Sep 2007
    Posts
    125
    Thanks
    30
    Thanked 0 Times in 0 Posts
    That worked great - thanks!!!


  •  

    Posting Permissions

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