CODEWITHSUNDEEP
phpmysqlpdoprepared-statement
Narktor
Narktor

Reputation: 1037

Mysql UPDATE query, keep the existing value on NULL

I have the following code so far:

  $update = $connection->prepare("UPDATE recommendations_tbl
                                     SET IFNULL(?, PLEASE_DO_NOTHING()),
                                         IFNULL(?, PLEASE_DO_NOTHING()),
                                     WHERE recommendation_userid = ?
                                     ");


  $update->bindValue(1, $recommendationsDataInput["recommendationCategoryNameInput"]);
  $update->bindValue(2, $recommendationsDataInput["recommendationManufacturerNameInput"]);
  $update->bindValue(3, $recommendationUserID);


  $updateResult = $insertion->execute();

Now, I'm pretty new to prepared statements and I just learned about using IFNULL inside prepared statements here: Logic to NOT insert a value to column X in a prepared statement

I'm not sure if the syntax, apart from my little placeholder PLEASE_DO_NOTHING(), would actually work at all. However, my main issue right now is that I don't know if I can actually use IFNULL() in this way: If the inputvalue is defined, SET the new value. If the inputvalue is undefined (=NULL), don't do anything.

Is this possible and if so, how can I do it?

Upvotes: 5

Views: 1842

Answers (1)

Andrii Filenko
Andrii Filenko

Reputation: 984

Actually, you can do it either with PHP or with IFNULL.

With PHP you can do it using a different prepared statement according to your NULL object.

With IFNULL you can try setting the same value as in the current column, eg your columns are called recommendation_category_name and recommendation_manufacturer_name:

$connection->prepare("UPDATE recommendations_tbl SET
          recommendation_category_name = IFNULL(?, recommendation_category_name),
          recommendation_manufacturer_name = IFNULL(?, recommendation_manufacturer_name),
          WHERE recommendation_userid = ?
");

Upvotes: 8

Related Questions