Page 1 of 1

Error in sql syntax, Driving me crazy!

Posted: Sun Oct 31, 2010 2:16 pm
by barffy
Hi,

im new to php and im trying to just add some data to my mysql database. Ive done this with success quite a few times but i cant get this script to work, even though it is practically identical to the code ive been using sucessfullt in the same database! I keep getting the error '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 ''name', 'key') VALUES ('br','123')' at line 1 '

This is the code for my html form:

<html>
<body>

<Form name="form1" form action= "insertproductkey.php" method="post">

Software Name:
<br/>
<input type="text" Name ="Name" id="Name"><br>

Product Key:
<br/>
<input type="text" name="Key" id="key"><br>

<h1><input type="Submit" Name="submit" value="submit"></h1>
</form>
<br>

</body>
</html>

and this is the code for the php script:


<?php
$con = mysql_connect("localhost","root","root");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("test", $con);

$sql="INSERT INTO productkey (name, key)
VALUES
('$_POST[name]','$_POST[key]')";



if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
echo "Your data has been added to the Data Base, Thank you for your time.";

mysql_close($con)
?>

like i say, very simple stuff and i cant understand why it wont work! its basically just going to store some product keys in the database.

Any help would be greatly apprciated.

Regards

Harry.

Re: Error in sql syntax, Driving me crazy!

Posted: Sun Oct 31, 2010 4:10 pm
by internet-solution
quotes missing in $_POST variable keys. You have to use $_POST["name"] or $_POST['name']. $_POST[name] will not work.

Re: Error in sql syntax, Driving me crazy!

Posted: Sun Oct 31, 2010 4:48 pm
by twinedev
While it is correct that you should wrap string indexes with quotes, it generally works anyhow (see http://php.net/manual/en/language.types ... rray.donts for en explanation).

The issue here is that you are using a reserved mySQL word as a field name (name), when you do this, you need two wrap the fieldname in backticks (` to the left of the 1 key)
Note the backticks are for database/table/field names, you still use regular quotes on the values. Myself, I jsut try to keep in the habit of using them all the time. Also note, if you do a query where you are specifying both the table and field (ie, in joins), you do each one separate. `table`.`field` not `table.field`

Also note, never pass user produced data straight to a query. Sanitize it with mysql_real_escape_string() (http://php.net/mysql_real_escape_string).

And lastly, if you are using an array inside double quotes, you should wrap it with curly braces {}. (see http://www.php.net/manual/en/language.t ... ng.parsing for more info on it) Mainly it makes it a lot easier to read ;-)

Here is your code with the lines cleaned up.

Code: Select all

$sql="INSERT INTO` productkey` (`name`, `key`)
VALUES
('".mysql_real_escape_string($_POST['name'])."','".mysql_real_escape_string($_POST['key'])."')";
(oh, and don't forget to hit the PHP Code button when posting code ;)

Re: Error in sql syntax, Driving me crazy!

Posted: Sat Nov 06, 2010 5:49 am
by barffy
Thank you for your help. I Dont know what i did but i managed to fix it about 2 hours after i posted the thread. Reading your replies i shall try and learn something from this. Thanks