Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL update column only if value not empty where

Tags:

php

mysql

I have an UPDATE query and using Ajax, I wanted to know if any value is empty can I only update the values that not empty in the database. I don't know if this is possible to have a if statement or something to check to skip the empty values. I know I can just add another form element but just wanted to know if there was another solution.

Only if the data is POST from front end form. If data not POST don't update this Title = '.$title .',

$id = $_POST['id'];
$title = "";
$description = $_POST['Description'];
$date = $_POST['Date'];

 $query = 'UPDATE user SET

  `id` = '.$id.', 
  `Title` = '.$title .', 
  `Description` = '.$description.', 
  `Date` = '.$date =.' 
  WHERE `id` = '.$id;

 $result = mysql_query($query) or die("<b>A fatal MySQL error occured</b>.<br />Query:  ".$query."<br />Error: (".mysql_errno().") ".mysql_error());

Update: This is what worked for me. Thanks Karim Daraf

$query = " UPDATE user SET Title = Coalesce($title,Title ) etc...

like image 479
ifelse Avatar asked Jan 10 '23 19:01

ifelse


2 Answers

Try it with Coalesce .

   $query = " UPDATE user 
   SET 
 `Title`       = CASE WHEN `Title`='' or `Title` IS NULL THEN '$title' END, 
 `Description` = CASE WHEN `Description`='' Or `Description` IS NULL THEN '$description' END, 
  `Date`       = CASE WHEN `Date`='' Or Date` IS NULL THEN '$date' END
    WHERE `id` = '".$id."' ";

or :

  $query = " UPDATE user 
  SET 
 `id`         = Coalesce('$id''".$id."' , NULLIF(`id`,'')), 
`Title`       = Coalesce('$title''".$title."',NULLIF(`Title`,'') ) , 
`Description` = Coalesce('$description''".$description."' , NULLIF(`Description`,'') ) , 
 `Date`       = Coalesce('$date''".$date."',NULLIF(`Date`,'')) 
 WHERE `id` = '$id''".$id."' ";
like image 161
Karim Daraf Avatar answered Jan 26 '23 07:01

Karim Daraf


$query = 'UPDATE user SET

  `id` = '.$id.', 
  `Title` = COALESCE(NULLIF("'.$title.'", ""),`Title`), 
  `Description` = "'.$description.'", 
  `Date` = "'.$date.'" 

  WHERE `id` = "'.$id.'"';
like image 32
Leo Avatar answered Jan 26 '23 08:01

Leo