I'm having trouble submitting some data to MySQL via php, i get the following 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 ''student' (UID, FirstName, LastName, DOB, EmergencyContact, Address, City, State' at line 1"
I'm not sure where i've gone wrong, any help would be great.
$firstname = $_POST['firstname'];
$lastname = $_POST['lastname'];
$dob = $_POST['dob'];
$emergencycontact = $_POST['emergencycontactperson'];
$address = $_POST['addressline1'];
$city = $_POST['city'];
$state = $_POST['state'];
$zip = $_POST['zip'];
$homephone = $_POST['homephone'];
$cellphone = $_POST['cellphone'];
$guardian = $_POST['guardian'];
$inneighborhood = 0;
if ($zip == "49503")
$inneighborhood = 1;
$con = mysqli_connect("localhost", "cookarts_root", "password", "cookarts_database");
// Check connection
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$sql = "INSERT INTO 'student' (FirstName, LastName, DOB, EmergencyContact, Address, City, State, ZIP, CellPhone, HomePhone, Guardian, InNeighborhood)
VALUES
($firstname', '$lastname', '$dob', '$emergencycontact', '$address', '$city', '$state', '$zip', '$cellphone', '$homephone', '$guardian', '$inneighborhood')";
if ($con->query($sql) === TRUE) {
echo 'users entry saved successfully';
}
else {
echo 'Error: '. $con->error;
}
mysqli_close($con);
?>
If you need more info i'd be happy to provide it, thanks again for the help.
解决方案
TableNames are Identifiers so they should not be quoted with single quotes. Since the tableName you've used is not a reserved keyword, you can simply remove those wrapping quotes around,
INSERT INTO student (FirstName, LastName....
When you wrap something with single quotes, it forces that object to become a string literal. So when identifiers are wrap with single quotes, it means that they are not identifier anymore.
The server throws an exception because INSERT INTO expects an identifier not string literal.
When you have accidentally used a table name which is a MySQL Reserved Keyword, don't use single quote to delimit the identifier but with backticks.
As a sidenote, the query is vulnerable with SQL Injection if the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.