MySQL, how to insert null dates

Question

I am having trouble inserting null values into date fields into a MySQL table.

Here is the insert query:

$query = 'INSERT INTO table (column_s1, column_s2, column_d1, column_d2)
VALUES ("'.$string1.'", "'.$string2.'", '.$date1.', '.$date2.')';

Columns s1 and s2 take string values and d1 and d2 take dates. When I run this query with only the string fields, there is no problem.

The date values can be either set or null, so I have not included the quotation marks in the query, but have instead added them to the variable earlier on. This is the php code I am using to set the date values:

if (empty($date1)){
    $date1 = NULL;
}
else{
    $date1part = explode("/",$date1);
    $date1 = '"'.$date1part[2].'/'.$date1part[1].'/'.$date1part[0].'"';
}

When the date values are all set, the record is inserted correctly. However, when either of the dates is null, nothing is inserted.

Why can't I just insert null values into MySQL like this?

Whats the error message you're getting? Has the database been setup to allow null values? No constraints in place? — null
– null, Commented Jul 22, 2013 at 9:50
use cases.. case when $date1 != null then $date1 else null end — zxc
– zxc, Commented Jul 22, 2013 at 9:51
I can't get it to produce any error messages? As far as I can tell, it seems to think it's inserted it. Is there some way you can suggest for debugging? Yes, the database accepts null values. When I use the SQL command space it lets me insert NULL values there, it seems only to be when it's coming from my php. — ajor
– ajor, Commented Jul 22, 2013 at 9:56
@deceze: thank you for linking to this question. I had already tried that solution (I thought), but obviously in slightly different circumstances as it now appears to have solved my problem. The issue was that it was taking a php null value rather than inputting the null into SQL. Many thanks. — ajor
– ajor, Commented Jul 22, 2013 at 10:17

Wh1T3h4Ck5 · Accepted Answer · 2013-07-22 10:22:56Z

14

Try this:

$query = "INSERT INTO table (column_s1, column_s2, column_d1, column_d2) 
          VALUES ('$string1', '$string2', " . ($date1==NULL ? "NULL" : "'$date1'") . ", " . ($date2==NULL ? "NULL" : "'$date2'") . ");";

so for example if you put this into query:

$string1 = "s1";
$string2 = "s2";
$date1 = NULL;
$date2 = NULL;

result should be:

INSERT INTO table (column_s1, column_s2, column_d1, column_d2) VALUES ('s1', 's2', NULL, NULL);

edited Jul 22, 2013 at 10:22

answered Jul 22, 2013 at 10:16

Wh1T3h4Ck5

8,52910 gold badges63 silver badges82 bronze badges

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

1 Comment

LΞИIИ Over a year ago

Thank you, this is the best and smart answer that worked for me

Community · Accepted Answer · 2017-05-23 12:00:14Z

7

You should convert the null variable into a NULL string first Like this:

if(is_null($date1)){
    $date1 = 'NULL';
}

If you are using a MySQL date column, you must also specify that it should hold null when creating it, like this:

CREATE TABLE `table` (
id INT NOT NULL AUTO_INCREMENT,
date DATE NULL DEFAULT NULL,
PRIMARY KEY(id)
)

It is also very important that you perform the query with bound parameters, for example using pdo

Something like this:

$query = 'INSERT INTO table (column_s1, column_s2, column_d1, column_d2)
VALUES (?, ?, ?, ?)';
$stmt = $db->prepare($query);
$stmt->execute(array($string1,$string2,$date1,$date2));

edited May 23, 2017 at 12:00

CommunityBot

11 silver badge

answered Nov 8, 2013 at 13:34

Timo Huovinen

56k34 gold badges163 silver badges150 bronze badges

Comments

Brhaka · Accepted Answer · 2020-07-22 17:25:38Z

3

If NULL does not work, just pass your date as "0000-00-00":

$chequeDate = "0000-00-00";

edited Jul 22, 2020 at 17:25

Brhaka

1,6523 gold badges14 silver badges32 bronze badges

answered Jun 28, 2018 at 5:18

Optimaz Prime

94710 silver badges11 bronze badges

1 Comment

Mm-Art-In Over a year ago

This will throw a warning or an error in MySQL 5.7 or above. zero value dates are no loonger allowed by default and not recommended.

Eric Leschinski · Accepted Answer · 2014-01-03 04:09:35Z

2

Backslash N is another way to express NULL in MySQL.

Try putting the value (backslash N): \N into one of the parameters like this:

$data1 = "\N";
$sql="insert into tablename set column_s1='" . $data1 . 
  "', column_s2='" . data2 . 
  "', column_s3='" . $data3 . "'";

Reference: http://dev.mysql.com/doc/refman/5.1/en/load-data.html

edited Jan 3, 2014 at 4:09

Eric Leschinski

155k96 gold badges423 silver badges337 bronze badges

answered Jul 22, 2013 at 10:13

innovative kundan

6316 silver badges13 bronze badges

1 Comment

Wh1T3h4Ck5 Over a year ago

NO, that's same as OP query + still doesn't work with NULL values.

Philippe Blayo · Accepted Answer · 2014-07-29 18:08:16Z

1

In Derby, If you want to insert values except the ones you have declared Null (column_d1, column_d2), sql:

INSERT INTO DB.table (column_s1, column_s2) VALUES ('s1', 's2');

edited Jul 29, 2014 at 18:08

Philippe Blayo

11.1k14 gold badges52 silver badges66 bronze badges

answered Jul 29, 2014 at 18:03

Luis Daniel Dorta

111 bronze badge

Comments

Grokking · Accepted Answer · 2015-02-04 13:33:36Z

1

Probably answer is unneeded at this moment, but I found solution exactly I have been searching. Use an Expression to pass NULL like this:

['some_date_to_update' => new Expression('NULL')]

Hence, MySQL will understand what you want, and save (NULL) in DB instead of storing 0-dates. Hope this will help somebody.

answered Feb 4, 2015 at 13:33

Grokking

7158 silver badges17 bronze badges

Comments

Siva · Accepted Answer · 2019-01-17 08:38:37Z

0

In Mysql DATE data type Default NULL means

Some version set as 0000-00-00

Some version set as 1970-01-01

answered Jan 17, 2019 at 8:38

Siva

1,5271 gold badge19 silver badges29 bronze badges

Comments

norbert chitsike · Accepted Answer · 2022-05-11 11:07:36Z

0

Years later, if someone is still experiencing this issue, you want to use PDO and bind the variables, everything will be taken care of no need to handle the null variables yourself.

answered May 11, 2022 at 11:07

norbert chitsike

111 silver badge1 bronze badge

1 Comment

Vincent Over a year ago

An example of how to bind null variables would improve your answer.

Collectives™ on Stack Overflow

MySQL, how to insert null dates

8 Answers 8

1 Comment

Comments

1 Comment

1 Comment

Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

1 Comment

Comments

1 Comment

1 Comment

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related