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

Community · Accepted Answer · 2014-10-10 23:52:34Z

23

You can use COALESCE

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

edited Oct 10, 2014 at 23:52

CommunityBot

11 silver badge

answered Mar 20, 2013 at 13:43

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Chand Priyankara · Accepted Answer · 2013-09-29 20:04:59Z

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=?

answered Sep 29, 2013 at 20:04

Chand Priyankara

6,8522 gold badges43 silver badges64 bronze badges

Comments

muyani · Accepted Answer · 2018-03-27 14:45:52Z

2

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")`

answered Mar 27, 2018 at 14:45

muyani

314 bronze badges

Comments

advermark · Accepted Answer · 2013-03-20 13:44:50Z

1

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

answered Mar 20, 2013 at 13:44

advermark

2311 silver badge10 bronze badges

Comments

Bhadra · Accepted Answer · 2013-03-20 13:55:01Z

1

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

answered Mar 20, 2013 at 13:55

Bhadra

2,1041 gold badge13 silver badges19 bronze badges

Comments

ulentini · Accepted Answer · 2013-03-20 13:56:43Z

0

Try with this:

$updateList = array();
if(!empty($_POST['input1'])) {
    $updateList[] = 'a = '.$_POST['input1'];
}

//Do it for every input...

if(!empty($updateList)) {
    $sql = 'UPDATE table SET '.implode(', ', $updateList).' WHERE row = 1';
}

Note that I didn't escape/quote anything, since you said you know how to do it.

answered Mar 20, 2013 at 13:56

ulentini

2,4121 gold badge15 silver badges25 bronze badges

Comments

B_CooperA · Accepted Answer · 2013-09-29 20:31:40Z

0

Just make an array of them. Like so:

if(empty($_POST['input1']) && empty($_POST['input2']) && empty($_POST['input3'])) {
   return false;
  } else {
   $updates = array();

   if(!empty($_POST['input1'])) 
     $updates[] = 'a='".$_POST['input1']."';

   if(!empty($_POST['input2'])) 
    $updates[] = 'b='".$_POST['input2']."';

   if(!empty($_POST['input3'])) 
    $updates[] = 'c='".$_POST['input3']."';


        $query = "UPDATE table SET".$updates."WHERE row_id=''";
        $sth = $sth->connect->prepare($query);
        $sth->execute();

        ));
   }

answered Sep 29, 2013 at 20:31

B_CooperA

6591 gold badge10 silver badges31 bronze badges

Comments

Muhammad Raheel · Accepted Answer · 2013-03-20 14:10:10Z

-1

Try with a simple codition

$1  =   isset($_POST['input1'])? $_POST['input1'] : 'a';
$2  =   isset($_POST['input2'] ? $_POST['input2'] : 'b';
$3  =   isset($_POST['input3'] ? $_POST['input3'] : 'c';


if(count($_POST) > 0){

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

    );
}

edited Mar 20, 2013 at 14:10

answered Mar 20, 2013 at 13:43

Muhammad Raheel

19.9k7 gold badges69 silver badges106 bronze badges

1 Comment

Rikesh Over a year ago

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

Collectives™ on Stack Overflow

PHP/MYSQL UPDATE only if value is not null

8 Answers 8

Comments

Comments

Comments

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

Comments

Comments

Comments

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related