April 10, 2012

Only update fields that are not null

Question by user1325305

I have been trying to figure out how to update the fields only if they are not null (otherwise NULL overrides the unchanged fields). It doesn’t matter that the fields can not be null.

I have tried both the COALESCE and ISNULL, however I must not be using them in the right way as I’m not use to the Dreamweaver coding. The code is below:

<i>if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "catalogue")) {
  $updateSQL = sprintf("UPDATE catalogue SET catbox1=%s, catbox2=%s, catbox3=%s, catbox4=%s, catbox5=%s, catbox6=%s WHERE id=%s",
                       GetSQLValueString($_POST['catbox1'], "text"),
                       GetSQLValueString($_POST['catbox2'], "text"),
                       GetSQLValueString($_POST['catbox3'], "text"),
                       GetSQLValueString($_POST['catbox4'], "text"),
                       GetSQLValueString($_POST['catbox5'], "text"),
                       GetSQLValueString($_POST['catbox6'], "text"),
                       GetSQLValueString($_POST['id'], "int"));</i>

Answer by Starx

There are few mistakes in you code, But these can be typos.

  1. NO PHP Wrapper: You have include your mysqlcode with portion
  2. Use IS NOT NULL instead of ISNULL

Basic way of what you are trying to do is use a query like this

UPDATE `tablename` SET `fieldName` = 'value' WHERE `fieldName` IS NOT NULL

Author: Nabin Nepal (Starx)

Hello, I am Nabin Nepal and you can call me Starx. This is my blog where write about my life and my involvements. I am a Software Developer, A Cyclist and a Realist. I hope you will find my blog interesting. Follow me on Google+

...

Please fill the form - I will response as fast as I can!