View Full Version : SQL syntax error help

11-06-2011, 12:34 PM
hello, i'm relatively new to php and mysql. i have some code that is displaying the below error.

Could not insert data because 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 ''name', 'Weapons'' at line 1

the code that is giving this error is below


// connect to the mysql server
$link = mysql_connect($server, $db_user, $db_pass)
or die ("Could not connect to mysql because ".mysql_error());

// select the database
or die ("Could not select database because ".mysql_error());

$insert = mysql_query("insert into $table values ('NULL', '".$_POST['email']."', '".$_POST['password']."', '".$_POST['name']."', '".$_POST['Gender']."', '".$_POST['Age']."', '".$_POST['Race']."', '".$_POST['SpecialtyOne']."', '".$_POST['Feet']."', '".$_POST['Weight']."', '".$_POST['Eyes']."', '".$_POST['Hair']."', '".$_POST['Strength']."', '".$_POST['Stamina']."', '".$_POST['Perception']."', '".$_POST['Agility']."', '".$_POST['Intelligence']."', '".$_POST['Tech']."', '".$_POST['Luck']."', '".$_POST['Appearance']."', '".$_POST['Background']."', '".$_POST['Weapons']."', '".$_POST['Armour']."', '".$_POST['Items']."', '".$_POST['Enhancements']."', '".$_POST['Skills']."')ON DUPLICATE KEY UPDATE '".$_POST['name']."', '".$_POST['Weapons']."'")
or die("Could not insert data because ".mysql_error());


11-06-2011, 04:31 PM
Save yourself a headache and turn those $_POSTs into strings before doing the query.
$email = $_POST['email'];

saves all that . and ' and " and it becomes readable.

11-06-2011, 06:52 PM

// connect to the mysql server
$link = mysql_connect($server, $db_user, $db_pass)
or die ("Could not connect to mysql because ".mysql_error());

// select the database
or die ("Could not select database because ".mysql_error());

$insert = mysql_query("insert into $table values ('NULL', $email =$_POST['email']; $password= $_POST['password']; $name= $_POST['name']; $Gender= $_POST['Gender']; $Age= $_POST['Age']; $Race= $_POST['Race']; $SpecialtyOne= $_POST['SpecialtyOne']; $Feet= $_POST['Feet']; $Weight= $_POST['Weight']; $Eyes= $_POST['Eyes']; $Hair= $_POST['Hair']; $Strength= $_POST['Strength']; $Stamina= $_POST['Stamina']; $Perception= $_POST['Perception']; $Agility= $_POST['Agility']; $Intelligence= $_POST['Intelligence']; $Tech= $_POST['Tech']; $Luck= $_POST['Luck']; $Appearance= $_POST['Appearance']; $Background= $_POST['Background']; $Weapons= $_POST['Weapons']; $Armour= $_POST['Armour']; $Items = $_POST['Items']; $Enhancements= $_POST['Enhancements']; $Skills= $_POST['Skills']."')ON DUPLICATE KEY UPDATE ; $name= $_POST['name']; $Weapons= $_POST['Weapons'];'")
or die("Could not insert data because ".mysql_error());


you mean like that?

11-06-2011, 09:08 PM
No, like this:


$email =$_POST['email'];
$password= $_POST['password'];
$name= $_POST['name'];
$Gender= $_POST['Gender'];
$Age= $_POST['Age'];
$Race= $_POST['Race'];
$SpecialtyOne= $_POST['SpecialtyOne'];
$Feet= $_POST['Feet'];
$Weight= $_POST['Weight'];
$Eyes= $_POST['Eyes'];
$Hair= $_POST['Hair'];
$Strength= $_POST['Strength'];
$Stamina= $_POST['Stamina'];
$Perception= $_POST['Perception'];
$Agility= $_POST['Agility'];
$Intelligence= $_POST['Intelligence'];
$Tech= $_POST['Tech'];
$Luck= $_POST['Luck'];
$Appearance= $_POST['Appearance'];
$Background= $_POST['Background'];
$Weapons= $_POST['Weapons'];
$Armour= $_POST['Armour'];
$Items = $_POST['Items'];
$Enhancements= $_POST['Enhancements'];
$Skills= $_POST['Skills'];

$insert = mysql_query("insert into $table values ('NULL', $email, $password, $name, $Gender, $Age, $Race, $SpecialtyOne, $Feet, $Weight, $Eyes, $Hair, $Strength, $Stamina, $Perception, $Agility, $Intelligence, $Tech, $Luck, $Appearance, $Background, $Weapons, $Armour, $Items, $Enhancements, $Skills") or die("Could not insert data because ".mysql_error());

I did not put the ON DUPLICATE KEY UPDATE in there because it is not correct and it's the source of your error. You need to put a column = value as the update.

ON DUPLICATE KEY UPDATE deals with unique index or primary key and I don't think that's what you want.

11-06-2011, 09:14 PM
thanks i appreciate it. as i said i'm very new php and only got to where i was by luck and google. is there any chance i could be cheeky and ask you to put the correct on duplicate key update in? i understand if you dont have the time but thank you once again

Old Pedant
11-06-2011, 10:52 PM
So if there *IS* a duplicate, then *WHAT* fields do you want to update????

And what is your PRIMARY KEY in that table?

My gut feeling is that you probably don't really want to use ON DUPLICATE KEY. If the use really typed in all of that information, then he/she is probably *not* registered in your system and you are getting a false duplicate.

11-06-2011, 10:57 PM
that form submits for them to 'buy' something from a 'shop' for their character. it only needs to update Weapons but they need to enter a user name so it knows who to add it to. i'f i'm doing this wrong please feel free to correct me

Old Pedant
11-06-2011, 11:56 PM
But that form, according to your code, is requiring them to enter name, email, password, gender, race, etc., etc.

That makes sense for a registration form, but not for each and every time they come to the "shop" to buy something.

Heck, it doesn't even make sense that they would enter all the info when they login next week if they register this week.

You should have the registration process separate from the login process and both of them separate from the buying process.

And also, once they are logged in, you should be maintaining that login info via one or more $_SESSION values. You shouldn't depend on the validated login info coming from the browser.

So I can't say for sure you are doing it wrong, but it sure feels that way to me.

11-07-2011, 12:00 AM
the registration process is seperate, the webpage itself doesnt require you to fill all that information in. i removed it but when i tried to use it i got an incorrect column count error so i put it in the php but not the webpage itself

Old Pedant
11-07-2011, 03:19 AM
So then why would you *EVER* want to do an INSERT just because somebody is buying something?????

The whole premise makes no sense.

Since you seem to be using only one table in your database (in my opinion, a really rotten design--what happens when you want to add, say, "Charisma" as a buyable property?) then I would assume that once a person is registered you would NEVER want to insert that person's name and other information again. So you should *only* be doing updates.

If this is an update for a weapon, then just do the update for a weapon and do nothing else.

And why is the table name a PHP variable??? Please don't tell me you have more than one table with all these same fields sitting around.


$sql = "UPDATE theOneAndOnlyTable "
. " SET Weapons = '" . mysql_real_escape_string($_POST["Weapons"]) . "' " _
. " WHERE Name = '" . $_SESSION["UserName"] . "'";

And never the INSERT should meet, except when registering a new user.

Curiosity: How do you keep track of multiple weapons? Please don't tell me you have them in a single list in that one field. But I know that's what you are going to say.

11-07-2011, 10:12 AM
yes i do have a single list in that field, as i said though i am very new to using php with mysql. if i'm doing it wrong tell me, i want to learn and that wont happen if i dont know where i've gone wrong.

i only have one table, it is a php variable cos thats the code i found to do it and didnt know i needed to change it. once a person is registered then no none of their innformation needs to be entered again just new items/weapons etc

11-07-2011, 04:34 PM
FYI Reserved words

Some help


Old Pedant
11-07-2011, 10:25 PM
Well, here's the DB design I would have, were I you:

username VARCHAR(30),
name VARCHAR(50),
gender CHAR(1), /* M or F */
age INT,
email VARCHAR(100)

CREATE TABLE properties (
propertyName VARCHAR( 100 )

-- propertyName would have values such as "Weapon","Strength",etc.

CREATE TABLE userProperties (
userid INT REFERENCES users(userid),
propertyID INT REFERENCES properties(propertyID),
quantity INT,
power INT

So you register a user and they get put in the users table.

And you have a list of the kinds of properties their character can have in the properties table.

And then, for each property that they gain (or lose) you create or update an entry in the userProperties table.

You store their userid as a PHP session value, so you don't have to keep passing it around.

Say they purchase a spear:

$sql = "INSERT INTO userProperties (userid, propertyid) "
. "VALUES(" . $_SESSION["userid"] . "," . $_POST["propertyid"] . ")";

In the <form> where they choose what they are purchasing, you can show the propertyName but hide the propertyID (e.g., as a <input type="hidden"> or maybe in a <select> list you have <option value="17">Spear</option>).

This is called "NORMALIZATION" and, in the long run, it will make the maintenance and querying of the DB a *LOT* easier to do. It may seem like more work, when you are setting it up, but it is more than worth the effort.

Note that you never have more than one property per record (though the quantity of identical items could be more than one). It's perfectly okay to have, say, 3 spears (that is, you have a record that links a given user to a spear, and you have that record 3 times...though better would be to just change the quantity in a single record...but either will work).

11-07-2011, 10:43 PM
it's not only that it seems like more work it's confused the hell out of me. i've used php and html before but this is just beyond me

Old Pedant
11-07-2011, 11:06 PM
Just go read up a bit on "Normalization" as it applies to relational databases.

Mind you, you don't HAVE to do as I have suggested. But what you have at this point is not so much a database as just the equivalent of a text file where you are stuffing information. Which means that then *ALL* the work of figuring out how to handle adding, changing, deleting, finding information has to be done in your PHP code. You can do that, but it will make the PHP code many times bigger than it needs to be and much more complex. Whereas spending a week or two learning good database design principles will pay off in much better performance, much less coding, and far fewer bugs.

Here's one place to start:

Mind you, it doesn't teach database *DESIGN*. Just how to maneuver data.

Master that stuff first then we can talk about design.

11-08-2011, 12:30 AM
No what youve said makes sense as there are other things i'll need to do too that may get more complicated, any help or advice you can give is appreciated.