mysqli prepared statement - do not update NULL val

问题:

I have a prepared statement to update several fields. I get the data from a formular, but not all fields are required. So it's possible that some fields are not set. I set them default to NULL. Now I don't want to overwrite the old value by NULL. How can I tell MySql not to Update the value if it's NULL?

$insert_stmt = $mysqli->prepare("
UPDATE members SET username=?, email=?,  $password=?, $random_salt=?, level=?, customerID=?, name=?, surname=?, phone=?, quantities=? WHERE id=?
");
$insert_stmt->bind_param('ssssissss', $username, $email, $password, $random_salt, $level, $customerID, $firstname, $surname, $phone);
$insert_stmt->execute();

In my case it's the password and random_salt value that could be NULL. It will be very bad to overwrite the password just by NULL ;)

回答1:

You could change your query as follows:

UPDATE members SET
    username = IFNULL(?, username),
    email = IFNULL(?, email) -- and so on for all fields
WHERE...

It could also be more efficient to check the value of your parameters first, and build the query dynamically, including only fields for which you have a non-null value to update with.

回答2:

You could try this:

$insert_stmt = $mysqli->prepare("UPDATE members SET username=?, email=?,  password=IF(LENGTH('?')=0, password, '?'), random_salt=IF(LENGTH('?')=0, random_salt, '?'), level=?, customerID=?, name=?, surname=?, phone=?, quantities=? WHERE id=?");

the If condition translates to: IF( < YOUR_CONDITION >, < VALUE_IF_TRUE >, < VALUE_IF_FALSE >).

回答3:

Assuming id is PK you can use :

INSERT INTO members (
    id,
    username,
    email,
    password,
    random_salt,
    level,
    customerID,
    name,
    surname,
    phone,
    quantities
) VALUES (
    ?,
    ?,
    ?,
    ?,
    ?,
    ?,
    ?,
    ?,
    ?,
    ?,
    ?
)   ON DUPLICATE KEY UPDATE
    username = IF(username <> '',VALUES(username),username),
    email = IF(email <> '',VALUES(email),email),
    password = IF(password <> '',VALUES(password),password)
    ...

回答4:

Read your query fields and data carefuly. Set of fields and set of data in bind are not match.

UPDATE members SET username=?, email=?,  $password=?, $random_salt=?, level=?, customerID=?, name=?, surname=?, phone=?, quantities=? WHERE id=?

$insert_stmt->bind_param('ssssissss', $username, $email, $level, $customerID, $firstname, $surname, $phone);

Your bind list has no: password, random_salt, id