SET a field value equal to NULL

Using php v.5.x and MySQL v.5.x

I'm trying to set a column of field values to NULL.  The purpose is to reset values in a column(s) of an already existing and populated database.

The field I'm having trouble with is a datetime field if that makes any difference...and the column IS set to allow NULL values.

Here is my function:

function update_field($resource_table,$field,$f_value){
      $connector = new DbConnector();
      $sql_null = "SELECT * FROM $resource_table";
      $q_null = $connector->query($sql_null);
      while( $r_null = $connector->fetchArray($q_null) ){
            $connector->query("UPDATE $resource_table SET $field IS '$f_value' WHERE uid ='".$r_null['uid']."' LIMIT 1");
      }
      echo '<h1>FIELD UPDATED! ('.$field.'=&quot;'.$f_value.'&quot;)</h1>';
}

So my question is about the UPDATE statement. Please don't concern yourself with the function.  I know it works properly.  

I've tried:

$connector->query("UPDATE $resource_table SET $field = '$f_value' WHERE uid ='".$r_null['uid']."' LIMIT 1");

function call attempt 1: update_field($resource_table,'lastLogin',NULL);
function call attempt 2: update_field($resource_table,'lastLogin','NULL');

AND

$connector->query("UPDATE $resource_table SET $field IS '$f_value' WHERE uid ='".$r_null['uid']."' LIMIT 1");

function call attempt 3: update_field($resource_table,'lastLogin',NULL);
function call attempt 4: update_field($resource_table,'lastLogin','NULL');

Thanks in advance for your help.
jtwoods4Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steve BinkCommented:
I ran into the same problem: the quotes.

UPDATE mytable SET myfield = 'null'

The above will only set myfield equal to the string 'null', not a null value.  You need to rewrite the UPDATE to remove the quotes if the value is to be null.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
snoyes_jwCommented:
What routinet said.  And the syntax for updates is the '=', not the 'IS'.
todd_farmerCommented:
You can also do something like:

... SET myfield = IF('$f_value'='',NULL, '$f_value') ...
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

NovaDenizenCommented:
First off, why can't you just issue a single:
"UPDATE $resource_table SET $field = '$f_value'";
No loop required.

Second, I'm not sure you can process the results of a query while you run another query on the same connection.  I thought you had to completely read in the query results before you could do anything else with that connection.

In other words, do it with two connections like this:

function update_field($resource_table,$field,$f_value){
     $connector1 = new DbConnector();
     $connector2 = new DbConnector();
     $sql_null = "SELECT * FROM $resource_table";
     $q_null = $connector1->query($sql_null);
     while( $r_null = $connecto1r->fetchArray($q_null) ){
          $connector2->query("UPDATE $resource_table SET $field IS '$f_value' WHERE uid ='".$r_null['uid']."' LIMIT 1");
     }
     echo '<h1>FIELD UPDATED! ('.$field.'=&quot;'.$f_value.'&quot;)</h1>';
}

You really ought to escape the f_value to make sure it doesn't contain any naughty characters (remember sql injection attacks).  

Also, how are you going to tell the difference between when the user wants to set a field to null, and when the user wants to set it to the actual string 'null' ?  You really need a separate boolean parameter to distinguish between the two cases.  That, or require the user to quote and escape $f_value before they pass it to your routine.
star_trekCommented:
You can just use a update query, select query is not needed at all.
function update_field($resource_table,$field,$f_value){
     $connector1 = new DbConnector();
     $connector1->query("UPDATE $resource_table SET $field = IF($f_value,'$f_value',NULL)");
     echo '<h1>FIELD UPDATED! ('.$field.'=&quot;'.$f_value.'&quot;)</h1>';
}
jtwoods4Author Commented:
SOLUTION:

function update_field($resource_table,$field,$f_value){
     $connector = new DbConnector();
     $sql_null = "SELECT * FROM $resource_table";
     $q_null = $connector->query($sql_null);
     while( $r_null = $connector->fetchArray($q_null) ){
      $connector->query("UPDATE $resource_table SET $field = $f_value WHERE uid ='".$r_null['uid']."' LIMIT 1");
     }
     echo '<h1>FIELD UPDATED! ('.$field.'=&quot;'.$f_value.'&quot;)</h1>';
}

FUCNTION CALLs:

update_field($target_table,'date1',"'2006-03-27 12:00:00'");

or

update_field($target_table,'date2','NULL');


PS...the while loop is neccesary to loop through the entire table, changing all values in a given row.

Thanks everyone.
todd_farmerCommented:
Glad you got a solution - thanks for the points.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.