PHP/MYSQL UPDATE only if value is not null

Question

I'm building a form to update multiple columns of data. This code has been partially successful for my purposes. The only thing left to do is include (if IS NOT NULL) for the update query. I'm not sure how to go about this.

In other words, I only want the UPDATE to execute if the $_POST value is not empty.

<form>
<input type='text' name='input1' />
<input type='text' name='input2' />
<input type='text' name='input3' />
<input type='submit' value='submit' />
</form>

<?php
//db connect

$1=$_POST['input1'];
$2=$_POST['input2'];
$3=$_POST['input3'];

mysql_query("UPDATE table
             SET a = $1
                 b = $2
                 c = $3
             WHERE row = 'row_id");

);


?>

Thanks in advance for the help.

(To save you from some extra typing, my original code escapes characters so warnings of SQL injections aren't necessary. I'm also in the process of familiarizing myself with "mysqli_query", so no need to comment on that either.)

Solution 1

You can use COALESCE

       UPDATE table
         SET a = COALESCE($1, a),
             b = COALESCE($2, b),
             c = COALESCE($3, c)
         WHERE row = ''

Solution 2

COALESCE

is the solution. Its like equivalent to Oracle's NVL. You can use it like below in a prepared statement using parameters

UPDATE
    tbl_cccustomerinfo
SET
    customerAddress = COALESCE(?,customerAddress),
    customerName =  COALESCE(?,customerName),
    description =  COALESCE(?,description)
WHERE
    contactNumber=?

Solution 3

COALESCE

COALESCE is the best option.

MEANING: COALESCE(a,b) simply means that PUT a,but if a is NULL(not POSTED/PUT in your case)then replace it with b.

 `mysql_query("UPDATE table
         SET a =COALESCE($1,a)
             b = COALESCE($2,b)
             c = COALESCE($3,c)
         WHERE row = 'row_id")`

Solution 4

<?php
$query="UPDATE table SET"
$query.=(!empty($_POST['input1']))? "a=$1,":"";
$query.=(!empty($_POST['input2']))? "b=$2,":"";
$query.=(!empty($_POST['input3']))? "c=$3,":"";
$query=substr($query,0,-1);
$query.="WHERE row = 'row_id'"
mysql_query($query);
?>

Solution 5

if(!empty($_POST['input1'])){ ...mysql query.. }

View more solutions

Comments

[Rikesh]

Note: It will update if any one value is not null and other are.