We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

SET a field value equal to NULL

jtwoods4
jtwoods4 asked
on
Medium Priority
2,329 Views
Last Modified: 2008-02-01
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.
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2004
Commented:
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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Top Expert 2005

Commented:
What routinet said.  And the syntax for updates is the '=', not the 'IS'.
Top Expert 2006
Commented:
You can also do something like:

... SET myfield = IF('$f_value'='',NULL, '$f_value') ...
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.
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>';
}

Author

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.
Top Expert 2006

Commented:
Glad you got a solution - thanks for the points.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.