php - "No data supplied for parameters in prepared statement"

Question

So I am reworking a script to include prepared statements. It was working fine before, but now I am getting "No data supplied for parameters in prepared statement" when the script runs. What is the issue here?

<?php
require_once("models/config.php");


$firstname = htmlspecialchars(trim($_POST['firstname']));
$firstname = mysqli_real_escape_string($mysqli, $firstname);
$surname = htmlspecialchars(trim($_POST['surname']));
$surname = mysqli_real_escape_string($mysqli, $surname);
$address = htmlspecialchars(trim($_POST['address']));
$address = mysqli_real_escape_string($mysqli, $address);
$gender = htmlspecialchars(trim($_POST['gender']));
$gender = mysqli_real_escape_string($mysqli, $gender);
$city = htmlspecialchars(trim($_POST['city']));
$city = mysqli_real_escape_string($mysqli, $city);
$province = htmlspecialchars(trim($_POST['province']));
$province = mysqli_real_escape_string($mysqli, $province);
$phone = htmlspecialchars(trim($_POST['phone']));
$phone = mysqli_real_escape_string($mysqli, $phone);
$secondphone = htmlspecialchars(trim($_POST['secondphone']));
$secondphone = mysqli_real_escape_string($mysqli, $secondphone);
$postalcode = htmlspecialchars(trim($_POST['postalcode']));
$postalcode = mysqli_real_escape_string($mysqli, $postalcode);
$email = htmlspecialchars(trim($_POST['email']));
$email = mysqli_real_escape_string($mysqli, $email);
$organization = htmlspecialchars(trim($_POST['organization']));
$organization = mysqli_real_escape_string($mysqli, $organization);
$inriding = htmlspecialchars(trim($_POST['inriding']));
$inriding = mysqli_real_escape_string($mysqli, $inriding);
$ethnicity = htmlspecialchars(trim($_POST['ethnicity']));
$ethnicity = mysqli_real_escape_string($mysqli, $ethnicity);
$senior = htmlspecialchars(trim($_POST['senior']));
$senior = mysqli_real_escape_string($mysqli, $senior);
$student = htmlspecialchars(trim($_POST['student']));
$student = mysqli_real_escape_string($mysqli, $student);


$order= "INSERT INTO persons (firstname, surname, address, gender, city, province,  postalcode, phone, secondphone, email, organization, inriding, ethnicity, senior, student_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
$stmt = mysqli_prepare($mysqli, $order);
mysqli_stmt_bind_param($stmt, "sssd", $firstname, $surname, $address, $gender, $city, $province, $postalcode, $phone, $secondphone, $email, $organization, $inriding, $ethnicity, $senior, $student);
mysqli_stmt_execute($stmt); 
echo $stmt->error;

$result = mysqli_query($mysqli,$stmt);
if ($result === false) {
echo "Error entering data! <BR>";
echo mysqli_error($mysqli);
 } else {
echo "User $firstname added <BR>";
 }
?>

Thanks in advance.

Answer 1

You have only bound four arguments, by the control string "sssd", but you have many parameters. When binding variables with mysqli, you need one character for each parameter, for example:

mysqli_stmt_bind_param($stmt, "sssdsssssssssdd", $firstname, $surname, $address, 
    $gender, $city, $province, $postalcode, $phone, $secondphone, $email, 
    $organization, $inriding, $ethnicity, $senior, $student);

(I'm assuming senior and student are integers, and need the "d" code.)

You don't need to treat any of your variables with mysqli_real_escape_string() -- that's the point of using parameters. If you do escaping as well, you'll get literal backslash characters in your data in the database.

And you never need to use htmlspecialchars() in any case - you would use that when outputting to HTML, not when inserting to the database. You're going to get literal sequences like & in your data in the database.

---

Re your next error:

"Catchable fatal error: Object of class mysqli_stmt could not be converted to string in..."

This is caused by the following:

$result = mysqli_query($mysqli,$stmt);

That function expects the second argument to be a string, a new SQL query. But you've already prepared that query, so you need the following:

$result = mysqli_stmt_execute($stmt);