How do I prevent MySQL from updating column with empty string?

Question

I have a form to update details of a user and they do not have to fill in every field. Because of that, it will be passing an empty string to the database via POST. This is my query:

$q = "UPDATE mdl_user SET firstname='$fname', lastname='$lname', email='$email',
      address='$address', city='$city', school='$school', phone1='$phone'
      WHERE id='$uid'";

Is there a way for MySQL to check if for example $fname is an empty string and if it's empty don't update that field?

If not then how should I go about doing this in PHP?

Alex L · Accepted Answer · 2012-03-16 13:13:45Z

11

You can use IF statement for each field you need to check. For example :

$q = "UPDATE mdl_user SET firstname=IF(LENGTH('$fname')=0, firstname, '$fname'), lastname=IF(LENGTH('$lname')=0, lastname, '$lname'), email=IF(LENGTH('$email')=0, email, '$email'), address='$address', city='$city', school='$school', phone1='$phone' WHERE id='$uid'";

answered Mar 16, 2012 at 13:13

Alex L

6,5221 gold badge16 silver badges8 bronze badges

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

2 Comments

user1219572 Over a year ago

Hey, cheers. Can you explain what the second and third parameters in the IF clause means?

Alex L Over a year ago

Hi. IF(<YOUR_CONDITION>, <VALUE_IF_TRUE>, <VALUE_IF_FALSE>). So if string length=0 then don't change it.

Roderick Obrist · Accepted Answer · 2012-03-16 13:05:35Z

3

I would do this

<?php
if ($fname) {
 $fname = "firstname='$fname',";
} else {
 $fname = '';
}
$q = "UPDATE mdl_user SET $fname lastname='$lname', email='$email', address='$address', city='$city', school='$school', phone1='$phone' WHERE id='$uid'";
?>

But you should really be worried about how insecure your code is.

answered Mar 16, 2012 at 13:05

Roderick Obrist

3,8482 gold badges18 silver badges18 bronze badges

7 Comments

Mike B Over a year ago

Insecure? Are you sure? How do you know he's not escaping each of those variables?

Silver Quettier Over a year ago

One can assume these variables are properly sanitized after collection from the form. I hope ? The empty string will just add nothing to the QUERY, and since the column name is in it, it won't update it in the DB.

Roderick Obrist Over a year ago

I hate to be rude, and no offence should be taken because we have all been there, but with a question this primative one could assume that he is not escaping.

Roderick Obrist Over a year ago

@user1219572 It would not because you are taking out the whole assignment, it can either be a whole value, or nothing, note that the sql has had the firstname= part removed

user1219572 Over a year ago

Can you suggest any other ways because your code would imply that I would have to do an if statement for every single input? :/

|

user652792 · Accepted Answer · 2012-03-16 13:08:26Z

-2

First run a SELECT statement and do a mysql_num_row() if this returns a value >0 then exit()

answered Mar 16, 2012 at 13:08

user652792

1 Comment

user1219572 Over a year ago

Select statement for what? :/ I just want to make sure I don't update a column with an empty string and thus overwriting any previous values in that column

Collectives™ on Stack Overflow

How do I prevent MySQL from updating column with empty string?

3 Answers 3

2 Comments

7 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related