[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SET a field value equal to NULL

Posted on 2006-03-27
7
Medium Priority
?
2,280 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.
0
Comment
Question by:jtwoods4
7 Comments
 
LVL 51

Accepted Solution

by:
Steve Bink earned 1000 total points
ID: 16302318
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
 
LVL 33

Expert Comment

by:snoyes_jw
ID: 16302331
What routinet said.  And the syntax for updates is the '=', not the 'IS'.
0
 
LVL 30

Assisted Solution

by:todd_farmer
todd_farmer earned 1000 total points
ID: 16302366
You can also do something like:

... SET myfield = IF('$f_value'='',NULL, '$f_value') ...
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 22

Expert Comment

by:NovaDenizen
ID: 16303337
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
 
LVL 11

Expert Comment

by:star_trek
ID: 16334609
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
 

Author Comment

by:jtwoods4
ID: 16337214
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
 
LVL 30

Expert Comment

by:todd_farmer
ID: 16368049
Glad you got a solution - thanks for the points.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month20 days, 4 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question