"Call to a member function bind_param() on bool" error when using a necessary WHERE clause

Question

I am trying to update an entry in a database when a user edits their information, but when I tested it, it threw an error for trying to use a bind_param statement with a WHERE clause (as there might not be any such instance). My code passes in the User ID of the current user to be used in the WHERE clause, so no matter what, there will always be an instance of that User ID, but the system refuses to recognize that. My code is below:

if(preg_match("#^[a-zA-Z0-9\d._-]+$#", $userpassword)) {
    $sql = "SELECT * FROM User WHERE (Email = '$email' and UserID != '$userid')";
    $res = $mysqli->query($sql);
    if ($res->num_rows > 0) {
        $row = $res->fetch_assoc();
        echo "<script type='text/javascript'>
            alert('This email is already in use. For security purposes, you have been signed out.');
            window.location.href = '../volunteer.html';
        </script>";

    } else {
        $sql = "SELECT * FROM User WHERE (UserID = '$userid')";
        $res = $mysqli->query($sql);
        if ($res->num_rows > 0) {
            $stmt = $mysqli->prepare("UPDATE User SET FirstName, LastName, Email, Phone, UserPassword WHERE UserID = '$userid' VALUES (?,?,?,?,?)");
            $stmt->bind_param("sssss", $firstname, $lastname, $email, $phone, $userpassword);
            echo $stmt;
            $stmt->execute();
            echo "<script type='text/javascript'>
                alert('The changes were successfully saved. For security purposes, you have been signed out.');
                window.location.href = '../volunteer.html';
            </script>";
        }
    }

as well as the database the information is stored in:

CREATE DATABASE IF NOT EXISTS VOLUNTEER_HOURS;
USE VOLUNTEER_HOURS;

DROP TABLE IF EXISTS ACTIVITY;
DROP TABLE IF EXISTS USER;

CREATE TABLE IF NOT EXISTS USER(
    UserID int NOT NULL AUTO_INCREMENT,
    FirstName varchar(30) NOT NULL,
    LastName varchar(30) NOT NULL,
    Email varchar(30) NOT NULL,
    Phone bigint NOT NULL,
    UserPassword varchar(30) NOT NULL,
    PRIMARY KEY (UserID)
) ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS ACTIVITY(
    ActivityID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ActivityType varchar(50) NOT NULL,
    ActivityDate date NOT NULL,
    Length double NOT NULL,
    UserID int,
    FOREIGN KEY (UserID) REFERENCES USER(UserID)
) ENGINE = INNODB;

How can I use the WHERE clause to edit the record of the current user without throwing this error?

Answer 1

The syntax for UPDATE is

UPDATE <table name>
       SET <1st column name> = <1st value>
           ...
           <1st column name> = <1st value>
       WHERE <conditions>;

A VALUES clause is typically used in INSERT statements. You seem to confuse them.

So change

$stmt = $mysqli->prepare("UPDATE User SET FirstName, LastName, Email, Phone, UserPassword WHERE UserID = '$userid' VALUES (?,?,?,?,?)");

to

$stmt = $mysqli->prepare("UPDATE User SET FirstName = ?, LastName = ?, Email =?, Phone = ?, UserPassword = ? WHERE UserID = ?");

• Note that you also should parameterize the user ID and all the other values in the other queries.

• Check for errors. Like that you would have gotten a message, that indicated that $mysqli->prepare() failed and why.

• If the password is stored as clear text: Don't do that. Only store the salted hash of a password.