insert NULL in sql query using PHP

Question

I want to build query using two array 1 for column and another for value. I have 3 integer columns in first array for which I don't want to store 0 as default so i want to set NULL, I want to check if any value entered for it, if not entered i want to set NULL for it . But when i create query i cant see NULL, i just see null replaced by blank. Seems like PHP is stripping out the NULL word ?

$set_null = array("asst_phone","mailing_zip","other_zip");

    foreach($columns as $key=>$col){
    if(in_array($col,$set_null) && $values[$key] == ""){
       $val[$key] = NULL;
    }
    else    
       $val[$key] = "'".$values[$key]."'";
    }
    $query_vals = "";
    foreach($val as $qval){
      if($qval == "") $query_vals .= NULL;
        $query_vals .= $qval;
            }
    echo $query_vals;

It generates query like below

INSERT INTO zoho_contacts (`contactid`,`smownerid`,`contact_owner`,`lead_source`,`first_name`,`last_name`,`accountid`,`account_name`,`email`,`title`,`department`,`phone`,`home_phone`,`other_phone`,`fax`,`mobile`,`date_of_birth`,`assistant`,`asst_phone`,`reports_to`,`smcreatorid`,`created_by`,`modifiedby`,`modified_by`,`created_time`,`modified_time`,`mailing_street`,`other_street`,`mailing_city`,`other_city`,`mailing_state`,`other_state`,`mailing_zip`,`other_zip`,`mailing_country`,`other_country`,`description`,`email_opt_out`,`skype_id`,`salutation`,`add_to_quickbooks`,`secondary_email`) values ('418176000000052001','418176000000047003','','','','','418176000000047027','','','','','02164 25452132','','','','9798659821','','',,'','418176000000047003','','418176000000047003','','','','','','','','','',,,'','','','','','Mr.','','')

My mysql column structure

column properties = NULL => Yes, DEFAULT => NULL, right ??

flowfree · Accepted Answer · 2012-05-30 13:05:37Z

3

Replace

$val[$key] = NULL;

with

$val[$key] = 'NULL';

answered May 30, 2012 at 13:05

flowfree

16.5k12 gold badges55 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

whiteatom Over a year ago

MySQL is looking for the string, not a php variable.

Amit Patil Over a year ago

if we enclose NULL with 'NULL' then it works for column with datatype varchar, for integer column it stores zero, which i dont want to.

David Over a year ago

@www.amitpatil.me: Does the integer column allow NULL? Or is the column defined in the database as providing a default value of 0 when no value is provided?

flowfree Over a year ago

@David yes, I'm going to ask that!

David Over a year ago

@www.amitpatil.me: The "resulting query" in the question still shows no instance of NULL in the inserted values, which it would need. What is the resulting query when you try this approach of wrapping NULL in quotes? Can you show that?

haris · Accepted Answer · 2012-05-30 13:30:31Z

0

Probably, a better idea to use mysql_escape_string for all values.

if($val != "")
$val= "".mysql_escape_string($val)."";
else
$val = "NULL";

answered May 30, 2012 at 13:30

haris

752 silver badges8 bronze badges

Comments

Brian Adkins · Accepted Answer · 2012-05-30 19:53:45Z

0

In php, NULL needs to be quoted (if you want it to work in this query)

edited May 30, 2012 at 19:53

answered May 30, 2012 at 13:06

Brian Adkins

6662 gold badges6 silver badges13 bronze badges

7 Comments

Amit Patil Over a year ago

if i enclose NULL with 'NULL' then it works for column with datatype varchar, for integer column it stores zero, which i dont want to.

nageeb Over a year ago

(regarding this answer) Not true. In PHP the value NULL is perfectly acceptable unquoted. The issue is that when inserting NULL into a string, the value gets converted to be a blank string, or ''. In order to get MySQL to insert a null value, the string value of "NULL" needs to be passed into the query.

Amit Patil Over a year ago

@nageeb : then whats the solution ??

nageeb Over a year ago

the solution is to pass 'NULL' into the sql query. $foo=NULL evaluates to null in php, but if you want to pass that null value into MySQL, then you need to pass the literal value of 'NULL' so it would have to be $foo = 'NULL'.

Amit Patil Over a year ago

no i tried that.....it stores NULL in varchar column but for integer column it stores 0.

|

Collectives™ on Stack Overflow

insert NULL in sql query using PHP

3 Answers 3

5 Comments

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related