CODEWITHSUNDEEP
phpmysqlmysqliprepared-statement
Karl Adler
Karl Adler

Reputation: 16786

mysqli prepared statement - do not update NULL values

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 ;)

Upvotes: 3

Views: 1860

Answers (4)

RandomSeed
RandomSeed

Reputation: 29759

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.

Upvotes: 5

Emil Kaminski
Emil Kaminski

Reputation: 1885

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 >).

Upvotes: 1

sectus
sectus

Reputation: 15454

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

Upvotes: 0

Stephan
Stephan

Reputation: 8090

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)
    ...

Upvotes: 0

Related Questions