insert date null value using mysql real escape string

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, Feb 23 '16 at 04: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, Feb 23 '16 at 04: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, Feb 23 '16 at 04:55

safin chacko · Answer 1 · 2016-02-23T05:07:27.277

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 '16 at 05:07

answered Feb 23 '16 at 04:59

safin chacko

1,345
1
11
18

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. – Devon Bessemer Feb 23 '16 at 05:01
I already set the DATE_PURCHASED into null but nothing works. – json Feb 23 '16 at 05:03

score 0 · Answer 2 · answered Feb 23 '16 at 05:07

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 '16 at 05:07

Kausha Mehta

2,828
2
20
31

Woh! it doesnt return null.. XD – json Feb 23 '16 at 05:22
@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 Feb 23 '16 at 05:25
@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. – Kausha Mehta Feb 23 '16 at 05:28

score 0 · Answer 3 · answered Feb 23 '16 at 06:21

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());

insert date null value using mysql real escape string

3 Answers3