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?
 
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.
0
 
snoyes_jwCommented:
What routinet said.  And the syntax for updates is the '=', not the 'IS'.
0
 
todd_farmerCommented:
You can also do something like:

... SET myfield = IF('$f_value'='',NULL, '$f_value') ...
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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.
0
 
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>';
}
0
 
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.
0
 
todd_farmerCommented:
Glad you got a solution - thanks for the points.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.