MySQL update column only if value not empty where [duplicate]

Question

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

Please ensure you validate your inputs. Don't do it on JS side, do it on server side. — frlan
– frlan, Commented Sep 15, 2014 at 22:38
Your above code is a extremely security risk. 1) Do not use mysql_ functions, they are deprecated. Instead use mysql i or pdo, prepared statements. If you don't use prepared statements make sure to use a escape string. — TheNiceGuy
– TheNiceGuy, Commented Sep 15, 2014 at 23:08
The code above is an example I'm only looking to see if there's away to update only the value that not empty. I know about client side validation the example are different variable from what I'm using. I think COALESCE is what I'm looking for but got to understand how to use it with my codes. — ifelse
– ifelse, Commented Sep 15, 2014 at 23:14

Karim Daraf · Accepted Answer · 2014-09-16 08:15:41Z

8

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."' ";

edited Sep 16, 2014 at 8:15

answered Sep 15, 2014 at 22:47

Karim Daraf

2262 silver badges9 bronze badges

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

7 Comments

ifelse Over a year ago

I seen Coalesce before but didn't understand how to use this function with my query. I'm trying it out now but getting syntax errors so probably need to fix my quotes.

ifelse Over a year ago

This is working syntax no errors! Now re-writing to my query to see if I can get the Coalesce to work. Will keep you posted. Getting this error on page "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near" Thanks.

ifelse Over a year ago

The query is similar to above just different names and I'm using $_POST[''] so I need to make some variables. The other part I'm doing now is writing the Ajax to send POST data.

Karim Daraf Over a year ago

did the columns have empty values or null values in your table ?

ifelse Over a year ago

Okay, I tried to UPDATE only "Description" field worked but the other fields are now empty. I added content in the database and yes it still UPDATES all columns.

|

Leo · Accepted Answer · 2019-07-10 20:12:36Z

1

$query = 'UPDATE user SET

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

  WHERE `id` = "'.$id.'"';

edited Jul 10, 2019 at 20:12

answered Jul 3, 2018 at 0:47

Leo

1,8393 gold badges20 silver badges25 bronze badges

Comments

Dharman · Accepted Answer · 2020-11-15 13:37:20Z

0

Not sure to understand: you have data and want to update, but only if some fied in the DB are empty? In the case perfom only a where:

 $query = 'UPDATE user SET
  `id` = '.$id.', 
  `Title` = '.$title .', 
  `Description` = '.$description.', 
 `Date` = '.$date =.' 
  WHERE `id` = '.$id.' AND Title = '';

for example

edited Nov 15, 2020 at 13:37

Dharman♦

33.9k27 gold badges106 silver badges157 bronze badges

answered Sep 15, 2014 at 22:37

Peter

1,37621 silver badges36 bronze badges

1 Comment

ifelse Over a year ago

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

Collectives™ on Stack Overflow

MySQL update column only if value not empty where [duplicate]

3 Answers 3

7 Comments

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

1 Comment

Linked

Related