Why Am I Getting \"Number of Bind Variables Mismatch\" Error in MySQLi Prepared Statements?

Number of Bind Variables Mismatch in Prepared Statement Insertion

When executing an INSERT statement using MySQLi prepared statements, users may encounter the error: "Number of bind variables doesn't match number of fields in prepared statement."

This issue arises when the number of bind variables provided in the bind_param() method does not align with the number of placeholders in the INSERT query. For example, consider the code snippet below:

if($stmt = $conn->prepare("INSERT INTO login(user, pass) VALUES(?, ?)")) {

  /* Bind parameters s - string, b - blob, i - int, etc */
  $stmt->bind_param("ss", $user, $pw);

  /* Execute it */
  $stmt->execute();

  /* Bind results */
  $stmt->bind_result($user, $pw);

  /* Close statement */
  $stmt->close();
  $userId = $conn->insert_id;
}

In this code, the INSERT statement has two placeholders (?): one for the user and one for the pass. However, the bind_param() method is binding two variables ($user, $pw) to the placeholders. This results in the mismatch error.

The solution is to remove the bind_result() method call since the INSERT statement does not return any results. The updated code below correctly inserts data into the table:

if($stmt = $conn->prepare("INSERT INTO login(user, pass) VALUES(?, ?)")) {

  /* Bind parameters s - string, b - blob, i - int, etc */
  $stmt->bind_param("ss", $user, $pw);

  /* Execute it */
  $stmt->execute();

  /* Close statement */
  $stmt->close();
  $userId = $conn->insert_id;
}

The above is the detailed content of Why Am I Getting \"Number of Bind Variables Mismatch\" Error in MySQLi Prepared Statements?. For more information, please follow other related articles on the PHP Chinese website!