Getting MySQL Error when trying to insert data from text fields into database

Question

I am getting this error when trying to insert data into my database from my website:

Error: INSERT INTO newtask (new_category, new_department, new_required, 
new_name, new_address, new_contact, new_email,  new_logged, new_description) 
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
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 '?, ?, ?, ?, ?, ?, ?, 
?, 
?)' at line 1

Here is my code:

<?php
$servername = 'localhost';
$username = 'root';
$password = '';
$dbname = 'tasks_db';


$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
} 

$sql = "INSERT INTO newtask (new_category, new_department, new_required, 
new_name, new_address, new_contact, new_email,  new_logged, new_description) 
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";

if ($conn->query($sql) === TRUE) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

Not sure what i am doing wrong here?

Do try and get out of the habit of cluttering up your code with needless things like === true. Many functions are designed to return values that evaluate as logically true or false so that's redundant. — tadman
– tadman, Commented Aug 17, 2018 at 20:16
Where are the variables that are supposed to replace all the ? placeholders? — Barmar
– Barmar, Commented Aug 17, 2018 at 20:32

tadman · Accepted Answer · 2018-08-17 20:19:20Z

3

This is a prepared statement, so you need to prepare it, bind your values, and then execute it:

$stmt = $conn->prepare("INSERT INTO newtask (new_category, new_department, new_required, 
new_name, new_address, new_contact, new_email,  new_logged, new_description) 
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");

// One "s" per placeholder value, plus one value per "s" after
$stmt->bind_param("sssssssss", $new_category, $new_department, ...);

if ($stmt->execute()) {
  echo "New record created successfully";
} else {
  echo "Error: " . $sql . "<br>" . $conn->error;
}

Where $new_category is whatever value is going into that column and so on. This is all covered in the documentation.

The mistake is you were trying to run a SQL query with placeholder values and no data. ? is not a valid value in MySQL. It's replaced at the driver level by the bind_param operation on statements produced with prepare. The query function executes code as-is with no changes.

answered Aug 17, 2018 at 20:19

tadman

212k23 gold badges237 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Allrounder Over a year ago

Thanks, now it makes a bit more sense. However this is the error i am getting now 'Undefined variable: sql.... ' on line 25 which is 'echo "Something went wrong. Please try again later." . $sql ."<br>" . $conn->error;'

tadman Over a year ago

Since in this code $sql was factored out, just remove that from the echo statement. The connection error should contain any relevant details required to debug this. Note: Echoing raw errors in a production environment is an extremely risky practice. Try to log these instead.

Collectives™ on Stack Overflow

Getting MySQL Error when trying to insert data from text fields into database

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related