Mysqli_stmt :: bind_param () [mysqli-stmt.bind-param]: The number of variables does not match the number of parameters
My php form is inserting multiple columns and encrypted password into my table. However, when I run it, it says that the variable number does not match the number of parameters. This is my code:
<?php
if (isset($_POST['insert'])) {
require_once 'login.php';
$OK = false;
$conn = new mysqli ($host, $user, $password, $database) or die("Connection Failed");
$stmt = $conn->stmt_init();
$sql = 'INSERT INTO users (user_email, user_name, user_pref, user_password)
VALUES(?, ?, ?, des_encrypt(substring(md5(rand()),1,8)))';
if ($stmt->prepare($sql)) {
// bind parameters and execute statement
$stmt->bind_param('ssss', $_POST['user_email'], $_POST['user_name'], $_POST['user_pref'], $_POST['user_password']);
// execute and get number of affected rows
$stmt->execute();
if ($stmt->affected_rows > 0) {
$OK = true;
}
}
if ($OK) {
header('Location: confirm.php');
exit;
} else {
$error = $stmt->error;
}
}
?>
<!DOCTYPE HTML>
<html>
<head>
<meta charset="utf-8">
<title>Add User</title>
</head>
<body>
<h1>Add User</h1>
<?php if (isset($error)) {
echo "<p>Error: $error</p>";
} ?>
<form id="form1" method="post" action="">
<p>
<label for="user_email">User email:</label>
<input name="user_email" type="text" class="widebox" id="user_email">
</p>
<p>
<label for="user_name">User name:</label>
<input name="user_name" type="text" class="widebox" id="user_name">
</p>
<p>
User role: <select name = "user_pref">
<option value = "BLU">Blue</option>
<option value = "YEL">Yellow<option>
<option value = "GRE">GREEN</option>
</select>
</p>
<p>
<input type="submit" name="insert" value="Register New User" id="insert">
</p>
</form>
</body>
</html>
When I test the form without the ENCRYPTED PASSWORD it works fine, so this line is causing the problem when I try to insert the password:
$stmt->bind_param('ssss', $_POST['user_email'], $_POST['user_name'], $_POST['user_pref'], $_POST['user_password']);
Should I change the string to something else for the password?
thank
source to share
$sql = 'INSERT INTO users (user_email, user_name, user_pref, user_password)
VALUES(?, ?, ?, des_encrypt(substring(md5(rand()),1,8)))';
Only defines 3 placeholders, but you are trying to write them to 4.
$stmt->bind_param('ssss', $_POST['user_email'], $_POST['user_name'], $_POST['user_pref'], $_POST['user_password']);
For each? you are inserting into a prepared SQL statement, you must pass the variable to bind_param.
source to share
You are passing four variables here:
$stmt->bind_param('ssss', $_POST['user_email'], $_POST['user_name'], $_POST['user_pref'], $_POST['user_password']);
but only three of them are required
$sql = 'INSERT INTO users (user_email, user_name, user_pref, user_password)
VALUES(?, ?, ?, des_encrypt(substring(md5(rand()),1,8)))';
Cm. "?" characters, it will be replaced with bild_params. You probably want to replace your SQL query with the following:
$sql = 'INSERT INTO users (user_email, user_name, user_pref, user_password)
VALUES(?, ?, ?, des_encrypt(substring(md5(?),1,8)))';
source to share
The number of parameters that your query will execute is determined by the number ?
in your query.
You have 3 ?
in your request:
$sql = 'INSERT INTO users (user_email, user_name, user_pref, user_password)
VALUES(?, ?, ?, des_encrypt(substring(md5(rand()),1,8)))';
and you are passing 5 parameters to bind_param
:
$stmt->bind_param($_POST['user_email'], $_POST['user_name'], $_POST['user_pref']);
There are 2 options:
-
Take 5 parameters in the request:
$sql = 'INSERT INTO users (user_email, user_name, user_pref, user_password) VALUES(?, ?, ?, ?, des_encrypt(?))';
-
Pass only 3 parameters to the function
bind_param
:$stmt->bind_param('ssss', $_POST['user_email'], $_POST['user_name'], $_POST['user_pref'], $_POST['user_password']);
source to share