I'm trying to build a simple PHP record insert only I can't seem to get it to work, it keeps saying that I have an error in my syntax.
This isn't going to be on a live production server, this is for internal (my use) only so injection/attacks are not a concern.
Code:
<?php
$servername = "server";
$username = "un";
$password = "p";
$dbname = "db";
$title = $_POST['title'];
$firstname = $_POST['firstname'];
$surname = $_POST['surname'];
$gender = $_POST['gender'];
$address = $_POST['address'];
$dob = $_POST['dob'];
$evetelephone = $_POST['eve-telephone'];
$daytelephone = $_POST['day-telephone'];
$occupation = $_POST['occupation'];
$email = $_POST['email'];
$hearabout = $_POST['hear-about'];
$consent = $_POST['consent'];
$readrules = $_POST['readrules'];
$injury = $_POST['injury'];
$questions = $_POST['questions'];
$gymrisk = $_POST['gymrisk'];
$rules = $_POST['rules'];
$remove = $_POST['remove'];
$fullname = $_POST['fullname'];
$signature = 'test';/*$_POST['signature'];*/
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "INSERT INTO o18-reg (id, title, first_name, surname, gender, addr, dob, eve_tel, day_tel, occupation, email_addr, how_heard, over_18, rules, injury, questions, gym_risk, agree_rules, right_remove, full_name, signature)
VALUES ($title, $firstname, $surname, $gender, $address, $dob, $evetelephone, $daytelephone, $occupation, $email, $hearabout, $consent, $readrules, $injury, $questions, $gymrisk, $rules, $remove, $fullname, $signature)";
// use exec() because no results are returned
$conn->exec($sql);
echo "New record created successfully";
} catch(PDOException $e) {
echo $sql . "<br>" . $e->getMessage();
}
$conn = null;
?>
Error:
INSERT INTO o18-reg (id, title, first_name, surname, gender, addr, dob, eve_tel, day_tel, occupation, email_addr, how_heard, over_18, rules, injury, questions, gym_risk, agree_rules, right_remove, full_name, signature) VALUES (Mr, Liam, Gallagher, Male, 5 Street, 25/11/1990, 0883821953, 032714821953, designer, [email protected], google told me, Yes, Yes, Yes, Yes, Yes, Yes, Yes, Liam Gallagher, test) SQLSTATE[42000]: Syntax error or access violation: 1064 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 '-reg (id, title, first_name, surname, gender, addr, dob, eve_tel, day_tel, occup' at line 1
Firstly, that table name with the hyphen; MySQL figures you want to do math here.
$sql = "INSERT INTO o18-reg
Which translates to o18 minus reg
so wrap it in ticks
$sql = "INSERT INTO `o18-reg` ...
or rename it to be an underscore.
Then you'll be faced with another problem, the VALUES. You have what seems to be strings, so you'll need to quote those.
I.e.
VALUES ('$title', '$firstname', '$surname' ...
and do the same for the rest of the strings.
Then you're faced with an SQL injection.
Use a prepared statement:
Edit:
I just noticed, you have 21 columns, but using 20 variables without making the necessary adjustement to compensate. You would have received an additional error about columns do not match, something to the effect of:
Fatal error: Uncaught exception 'PDOException' with message. Insert value list does not match column list: 1136 Column count doesn't match value count at row 1
If id is an AI, you will need to make a slight adjustment in your query, by adding '' in:
VALUES ('', '$title', '$firstname' ...
If there is a variable set aside for it (which doesn't seem to be present in your posted code), then add it. However, if the id column is an AI, then use the '' above.
An insight:
Make sure that your form does use a POST method and that all inputs bear the name attributes, and with no typos.
Add error reporting to the top of your file(s) which will help find errors.
<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
// rest of your code
Sidenote: Displaying errors should only be done in staging, and never production.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments