Update field if supplied data is not null in mySQL

Question

I want to update my password field only if there's a password (not NULL) in input but at the same time, 'username' can update in the table.

This is my current PHP code:

UPDATE `my_tbl` 
SET `username`= '".$dataArray['username']."', `password` = IF( IS NULL('".$dataArray['password']."'),  
   `password`, '".$dataArray['password']."') 
WHERE `id` = ".$dataArray['id']

password = coalesce(passwordparam, password)

jarlh
– jarlh

2016-11-25 08:19:26 +00:00
Commented Nov 25, 2016 at 8:19 — jarlh
– jarlh, Commented Nov 25, 2016 at 8:19
IF should be IIF?

RST
– RST

2016-11-25 08:23:10 +00:00
Commented Nov 25, 2016 at 8:23 — RST
– RST, Commented Nov 25, 2016 at 8:23
@RST Please don't assume

Paper
– Paper

2016-11-25 09:50:35 +00:00
Commented Nov 25, 2016 at 9:50 — Paper
– Paper, Commented Nov 25, 2016 at 9:50

Sanooj T · Accepted Answer · 2016-11-25 08:40:18Z

1

Use concatenation to solve this

$sql= UPDATE `my_tbl` SET `username`= '".$dataArray['username']."', 
                    `password` = IF( IS NULL('".$dataArray['password']."'),  `password`, '".$dataArray['password']."')
                    WHERE `id` = ".$dataArray['id'];

Change this query into

 $condition="";
    if($dataArray['password'] != ""){
       $condition=" , `password` = '".$dataArray['password']."'";
    }

     $sql= "UPDATE `my_tbl` SET `username`= '".$dataArray['username'] ."' ".$condition."
                        WHERE `id` = ".$dataArray['id'];
                        echo $sql;

answered Nov 25, 2016 at 8:40

Sanooj T

1,3471 gold badge14 silver badges27 bronze badges

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

Comments

Arun Kumar MG · Accepted Answer · 2016-11-25 09:12:24Z

1

if($dataArray['password'])       
{
$condition=" , `password` = '".$dataArray['password']."'";   }  else 
{    
$condition="";    
}    
$sql= "UPDATE `my_tbl` SET `username`= '".$dataArray['username'] ."' ".$condition."  WHERE `id` = ".$dataArray['id'];

edited Nov 25, 2016 at 9:12

answered Nov 25, 2016 at 8:54

Arun Kumar MG

1581 silver badge12 bronze badges

2 Comments

Sanooj T Over a year ago

why you repeated the answer?

Arun Kumar MG Over a year ago

sorry, same code but modified some times without comma might gives error before concatenate..

Razib Al Mamun · Accepted Answer · 2016-11-25 09:44:10Z

1

With codeigniter active records :

$data['username'] = $dataArray['username'];

if(!empty($dataArray['password'])) {
    $data['password'] = $dataArray['password'];
}

$this->db->where('id', $dataArray['id']);
$this->db->update('my_tbl',$data);

answered Nov 25, 2016 at 9:44

Razib Al Mamun

2,7111 gold badge19 silver badges24 bronze badges

Collectives™ on Stack Overflow

Update field if supplied data is not null in mySQL

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related