insert date null value using mysql real escape string [duplicate]

Question

how to insert null date mysql real scape string

if (empty($DATE_PURCHASED)) {
    $DATE_PURCHASED = NULL;
} 

//sql query
$queryString = sprintf("INSERT INTO IT_ASSET_RADIO (BRAND_NAME, SERIAL_NO, DATE_PURCHASED, LICENSE_NO) 
    values ('%s','%s', '%s', '%s')",
    mysql_real_escape_string($BRAND_NAME),
    mysql_real_escape_string($SERIAL_NO),
    mysql_real_escape_string($DATE_PURCHASED),
    mysql_real_escape_string($LICENSE_NO));

$query = mysql_query($queryString ) or die("Insert failed: " . mysql_error());

I got an error of this query invalid date in DATE_PURCHASED if value is null

Your DB field is surely a "date" field, so you can't just insert NULL in it — Waqas Shahid
– Waqas Shahid, Commented Feb 23, 2016 at 4:51
You can't insert NULL using an escape string function. Especially the way you're doing it. You're specifically declaring it as a string by encapsulating it in ', using escape string, and %s. NULL is not a string. — Devon Bessemer
– Devon Bessemer, Commented Feb 23, 2016 at 4:53
My alternative would be to stop using mysql_ and escape string functions, other than that, you'll have to think about this more logically of how to handle non-string values. — Devon Bessemer
– Devon Bessemer, Commented Feb 23, 2016 at 4:55

safin chacko · Accepted Answer · 2016-02-23 05:07:27Z

1

$date=mysql_real_escape_string($DATE_PURCHASED); 
if(trim($date)==''){ $date=NULL;}

use $date in your query.

And also

set DATE_PURCHASED column in table default NULL

edited Feb 23, 2016 at 5:07

answered Feb 23, 2016 at 4:59

safin chacko

1,3901 gold badge11 silver badges18 bronze badges

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

2 Comments

Devon Bessemer Over a year ago

That won't work in strict mode, not sure about others. If you specify an empty string, it will try to insert an empty string, not a NULL value. Defaults are useful for when the column is excluded from the insert statement.

json Over a year ago

I already set the DATE_PURCHASED into null but nothing works.

Kausha Mehta · Accepted Answer · 2016-02-23 05:07:43Z

0

Try to pass the date string in the format like 'YYYY-MM-DD HH:MM:SS', do it like below:

..........mysql_real_escape_string(date('Y-m-d H:i:s', strtotime($DATE_PURCHASED))).....

answered Feb 23, 2016 at 5:07

Kausha Mehta

2,9182 gold badges22 silver badges31 bronze badges

3 Comments

json Over a year ago

Woh! it doesnt return null.. XD

Kausha Mehta Over a year ago

@json If this answer is useful to you then please accept it and up vote it, so other knows that, you got the solution. None other waste their time to find the solution.

Kausha Mehta Over a year ago

@json It doesn't return null, but you got the diff error. You just have to check it before passing to the mysql query and if the variable is blank then set the variable blank and if they have any value then you have to pass it to DB with correct format.

json · Accepted Answer · 2016-02-23 06:21:41Z

0

if (empty($DATE_PURCHASED)) {      
      $DATE_PURCHASED = NULL;
    } else {
       $DATE_PURCHASED = $data->DATE_PURCHASED;
    }
    // Surround it in quotes if it isn't NULL.
    if ($DATE_PURCHASED === NULL) {
      // Make a string NULL with no extra quotes
      $DATE_PURCHASED = 'NULL';
    }
    // For non-null values, surround the existing value in quotes...
    else $DATE_PURCHASED = "'$DATE_PURCHASED'";

$queryString = sprintf("INSERT INTO IT_ASSET_RADIO (BRAND_NAME, SERIAL_NO, DATE_PURCHASED, LICENSE_NO) 
    values ('%s','%s', $DATE_PURCHASED , '%s')",
    mysql_real_escape_string($BRAND_NAME),
    mysql_real_escape_string($SERIAL_NO),
    mysql_real_escape_string($LICENSE_NO));

$query = mysql_query($queryString ) or die("Insert failed: " . mysql_error());

answered Feb 23, 2016 at 6:21

json

375 bronze badges

1 Comment

json Over a year ago

Solve my Problem! :D

Collectives™ on Stack Overflow

insert date null value using mysql real escape string [duplicate]

3 Answers 3

2 Comments

3 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

1 Comment

Linked

Related