...

View Full Version : SQL syntax error with PHP variables



ShootingBlanks
11-13-2007, 03:09 PM
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:

$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!...

CFMaBiSmAd
11-13-2007, 03:19 PM
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.)

ShootingBlanks
11-13-2007, 03:32 PM
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!!!

aedrin
11-13-2007, 04:12 PM
You need to quote your values, as such:


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

ShootingBlanks
11-13-2007, 04:13 PM
You need to quote your values, as such:


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?...

CFMaBiSmAd
11-13-2007, 04:46 PM
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.

phpBuddy
11-13-2007, 05:12 PM
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:
$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!
$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' )";

ShootingBlanks
11-13-2007, 05:22 PM
That worked great - thanks!!! :thumbsup:



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum