SQL syntax error when using MySQL and PHP
I am trying to do something, but I am facing a problem. I've tried everything I know, but I'm new to MySQL , so I'm stumped.
This code:
<?php
require('cfg.php');
mysql_connect($server, $user, $pass) or die(mysql_error());
mysql_select_db($database) or die(mysql_error());
if (isset($_GET['name'])){
$name = $_GET['name'];
}
else
if (isset($_POST['submit'])){
$name = $_POST['name'];
$name1 = $_POST['name1'];
$name2 = $_POST['name2'];
$name3 = $_POST['name3'];
mysql_query("INSERT INTO data (name, name1, name2, name3) VALUES($name, $name1, $name2, $name3 ) ") or die(mysql_error());
echo ("Data entered successfully!");
}
?>
<html>
<head>
<title>Random giffgaff simmer</title>
</head>
<body>
<form action="" method="post">
<p>Your Username: <input type="text" name="name"></p>
<p>Username 1: <input type="text" name="name1"></p>
<p>Username 2: <input type="text" name="name2"></p>
<p>Username 3: <input type="text" name="name3"></p>
<p>Username 4: <input type="text" name="name4"></p>
<p>Username 5: <input type="text" name="name5"></p>
<p>Username 6: <input type="text" name="name6"></p>
<p><input type="submit" name="submit" value="Submit"></p>
</form>
</body>
</html>
Brings this error:
You have an error in your SQL syntax; check the manual corresponding to your MySQL server version for the correct syntax to use next to ")" on line 1
Now, this will tell me that this SQL code has a syntax error:
INSERT INTO data (name, name1, name2, name3) VALUES($name, $name1, $name2, $name3 )
But I don't think I can see him?
source to share
You have not specified your request. You have to quote every field like this
INSERT INTO data (name, name1, name2, name3) VALUES('$name', '$name1', '$name2', '$name3' )
As a tribute to TheCommonSense, I am providing the mysqli version using the correct prepared statement to keep the data safe
$db = new mysqli(...);
$stmt = $db -> prepare("INSERT INTO data (name, name1, name2, name3) VALUES(?, ?, ?, ?)");
$stmt -> bind_param("ssss", $name, $name1, $name2, $name3);
$stmt -> execute();
$db -> close()
source to share
Strings must be quoted and escaped.
$name = (isset($_POST['name'])) ? $_POST['name'] : '';
$name = mysql_real_escape_string($name);
$query = "INSERT INTO blah (name, ...) VALUES ('{$name}', ...)";
By the way, you should take a look at SQL injection. Also, before you get too far down the road, you should go and ditch mysql_ * in favor of PDO. PDO offers multiple driver support * (MySQL / SQLite / MSSQL / etc) and can execute prepared statements (cleaner / safer than mysql_real_escape_string).
* it doesn't make SQL magically portable, but it does help.
source to share
I guess that $name
, $name1
etc. strings? You must include them in single quotes. Try:
"INSERT INTO `data` (`name`, `name1`, `name2`, `name3`) VALUES ('$name', '$name1', '$name2', '$name3')"
Remember also to avoid all user supplied input that could potentially act like SQL injection (see here: http://php.net/manual/en/security.database.sql-injection.php ) with before how to pass them to the request, or switch to an extension and use prepared statements (best option). mysql_real_escape_string()
mysqli
source to share
mysql_query("INSERT INTO data (name, name1, name2, name3) VALUES('$name', '$name1', '$name2', '$name3') ") or die(mysql_error());
or
mysql_query("INSERT INTO data (name, name1, name2, name3) VALUES('".$name."', '".$name1."', '".$name2."', '".$name3."') ") or die(mysql_error());
try it
source to share