Warning: mysqli_stmt_bind_result(): Number of bind variables doesn't match number of fields in prepared statement

Question

I am doing a login form and I am stuck with the code since I've just learned about the prepared statement yesterday. I keep getting this error:

Warning: mysqli_stmt_bind_result(): Number of bind variables doesn't match number of fields in prepared statement.

I need some enlightenment on how to use the function. I only included codes that are relevant to my problem.

// Validate credentials
if(empty($email_err) && empty($password_err) && empty($userLevel_err)){
    // Prepare a select statement
    $sql = "SELECT id, email, password FROM users WHERE email = ? AND userLevel=?";
    
    if($stmt = mysqli_prepare($link, $sql)){
        // Bind variables to the prepared statement as parameters
        mysqli_stmt_bind_param($stmt, "ss", $param_email, $param_userLevel);
        
        // Set parameters
        $param_email = $email;
        $param_userLevel = $userLevel;
        
        // Attempt to execute the prepared statement
        if(mysqli_stmt_execute($stmt)){
            // Store result
            mysqli_stmt_store_result($stmt);
            
            // Check if email exists, if yes then verify password
            if(mysqli_stmt_num_rows($stmt) == 1){                    
                // Bind result variables
                mysqli_stmt_bind_result($stmt, $id, $email, $hashed_password, $userLevel);
                if(mysqli_stmt_fetch($stmt)){
                    if(password_verify($password, $hashed_password)){
                        // Password is correct, so start a new session
                        session_start();
                        
                        // Store data in session variables
                        $_SESSION["loggedin"] = true;
                        $_SESSION["id"] = $id;
                        $_SESSION["email"] = $email;                            
                        
                        // Redirect user to welcome page
                        header("location: index.php");
                        
                    } else{
                        // Display an error message if password is not valid
                        $password_err = "The password you entered was not valid.";
                    }

The number of bind results have to match with the columns in your select query, you don't select `user_level` column, just remove `$userLevel` from your `mysqli_stmt_bind_result ` — catcon, Oct 23 '20 at 04:01
but I need to users to be redirected to different pages based on their userLevel though (this part I haven't started yet) — Khairin Chan, Oct 23 '20 at 04:05
Like I said: The number of bind results **have to match** with the columns in your select query. If you need that value, add it in your select query. — catcon, Oct 23 '20 at 04:09
For the goodness sake. Mysqli operations do not require that insane amount of code. You need only a [few lines here](https://phpdelusions.net/mysqli/password_hash), not several *pages*. — Your Common Sense, Oct 23 '20 at 11:08

score 0 · Accepted Answer · answered Oct 23 '20 at 11:05

The problem is that the number of bind variables doesn't match number of fields in prepared statement. You either need to bind fewer variables or you need to add the missing fourth column to your SQL

//                                VVV Add the fourth column here
$sql = "SELECT id, email, password, userLevel FROM users WHERE email = ? AND userLevel=?";

or bind fewer variables

//                                                         VVV Remove the fourth binding
mysqli_stmt_bind_result($stmt, $id, $email, $hashed_password);

Warning: mysqli_stmt_bind_result(): Number of bind variables doesn't match number of fields in prepared statement

1 Answers1