CODEWITHSUNDEEP
phpmysqlcodeigniter
Sonu Kumar
Sonu Kumar

Reputation: 91

Update field if supplied data is not null in mySQL

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']

Upvotes: 0

Views: 158

Answers (3)

Razib Al Mamun
Razib Al Mamun

Reputation: 2711

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

Upvotes: 1

Arun Kumar MG
Arun Kumar MG

Reputation: 158

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

Upvotes: 1

Sanooj T
Sanooj T

Reputation: 1327

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;

Upvotes: 1

Related Questions