How to prevent MySQL from updating a column with an empty row?

Question

I have a form for updating user information, and they do not need to fill out each field. Because of this, it will pass an empty string to the database via POST. This is my request:

$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 an empty string, and if it is empty, do not update this field?

If not, how should I do this in PHP?

Answer 1

You can use the IF statement for each field that you want to check. For instance:

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

Answer 2

I would do it

 <?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 really need to worry about how insecure your code is.

Answer 3

First run the SELECT statement and do mysql_num_row () if it returns 0 , then exit ()