如何防止MySQL用空字符串更新列? [英] How do I prevent MySQL from updating column with empty string?

问题描述

我有一个表格来更新用户的详细信息，他们不必填写每个字段.因此，它将通过POST向数据库传递一个空字符串.这是我的查询:

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

MySQL是否可以检查例如$fname是否为空字符串，如果为空，则不更新该字段?

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?

如果没有，那我应该如何用PHP做到这一点?

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

推荐答案

您可以对需要检查的每个字段使用IF语句.例如:

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

这篇关于如何防止MySQL用空字符串更新列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！