mysqli update using prepared statement failing at bind_para

Question

I am converting the SQL statements in an existing php program to mysqli prepared statements.

I have one statement that fails with a "Fatal error: Call to a member function bind_param() on boolean" error, but can't see the reason why

I have tried looking through all the relevant questions on here, but none of the answers seem to apply.

The database format is: 1 admin_id Int(11) 2 username varchar(30) 3 password varchar(255) 4 level Int(11)

The PHP code below, is the area of code where it fails. The INSERT code works ok, and, as can be seen, the UPDATE code was originally written in the same format. This has been commented out and trace echos used currently to pinpoint anything that might be the problem. All the string variables are passed through stripslashes and htmlspecialchars before that particular code is reached.

I have tried enclosing the column names in ticks, but that made no difference, so have removed them.

if ($nameErr == "" && $passErr == "") {
    $ok = false;
    if ($type == "Edit") {
        echo "In Edit  : ";
        echo "variables: ".$username.", ". $userlevel.", ".$edit_id;
        $stmt = $conn->prepare('UPDATE admin SET username = ?, level= ? WHERE admin_id = ? LIMIT 1');
        echo " - statement prepared";
        $stmt->bind_param('sii', $username, $userlevel, $edit_id);
        echo " - binding successful";
        $stmt->execute();
        echo " - executed";
        $ok = true;
        /*
        $stmt->bind_param('sii', $username, $userlevel, $edit_id);
        if (
                $stmt &&
                $stmt->bind_param('sii', $username, $userlevel, $edit_id) &&
                $stmt->execute()) {
            $ok = true;
        }
         */
    } else {
        $hashpw = password_hash($password, PASSWORD_DEFAULT);
        $stmt = $conn->prepare('INSERT INTO admin (username, password, level) VALUES (?, ?, ?)');
        if (
                $stmt &&
                $stmt->bind_param('ssi', $username, $hashpw, $userlevel) &&
                $stmt->execute()) {
            $ok = true;
        }
    }
    if ($ok) {
        header('location: users.php');
    } else {
        die(htmlspecialchars($conn->error));
    }
}

The echo trace gives: "In Edit : variables: admin, 5, 1 - statement prepared" followed by the "Fatal error: Call to a member function bind_param() on boolean in C:\wamp64\www\AGC\user_edit.php on line 67" message. (Line 67 is the "$stmt->bind_param('sii', $username, $userlevel, $edit_id);" line)

As stated above the INSERT statement works - it is the UPDATE that fails.

score -2 · Answer 1 · answered Aug 12 '19 at 23:44

Sorted!

For no other reason than pure desperation, I inserted a $stmt->close(); line immediately before the if ($type == "Edit") { line, and I no longer get the error.

I could understand why this works if there was a previous $stmt call, but there isn't. Also I don't know why it worked, without that close statement with the INSERT structure.

mysqli update using prepared statement failing at bind_para

1 Answers1