Updating multiple colums in mysql table with null values using PHP

Hi All,

I am adding a feature of profile update in the profile page of a project.

Now there might be a situation where user does not fill all the fields of his profile.

Like through the profile page a user can update Name, About Me, Location, Country, Date Of Birth, Interests or His Website Link.

The SQL query I am using is

UPDATE userinfo SET name='$name',aboutme='$aboutme',location='$location',country='$country',interests='$interests',dob='$dob',website='$website' WHERE uid='".$_SESSION['user_id']."'

Suppose,
if the name is left blank in the form while updating I am updating $name=NULL
if the country is left blank in the form while updating I am updating $country=NULL
if the interestsis left blank in the form while updating I am updating $interests=NULL

So now when I fire this query in the database using the PHP command mysql_query function, the variables I am setting to NULL are being set as "blank" i.e. value being updated is "" in the database.
Whereas I want the value in the DB to remain as NULL(the column is defined as NULL with the default value)

I would appreciate any insight onto this.


Thanks in Advance

 

You would do that at the form level, something as simple as an if empty statement after your $_POST values are collected will do

if (empty($name)){
    $name = NULL;
    }

PHP:

But then again, name is something you might not what left blank ?

if (empty($name)){
    echo "NAME must not be empty";
    }

PHP:

Try a google search for Form Validation to find the best method that suits your needs.

 

This PHP code I have already included just before updating in the database.

and btw its not necessary that the user populates all the fields in the form. He may populate only 1 field out of the 7 mentioned in the main post.

 

I think the column needs to be set as text and then $name = "NULL"; might work ?

 

nope, MyVodaFone it won't work, your first method should work.

However, why do you want to set it as NULL and just not to left it blank?

 

Your wrapping quotes around the variable, that should only be neccesary if the $variable contains data which is not NULL...if its NULL don't wrap it within quotes (otherwise it will be parsed as a string rather then literally).

e.g.

$variable = empty($variable) ? NULL : "'{$variable}'";

$sql = "UPDATE table SET column_name = {$variable}";

PHP: