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?

Solution 1

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

Solution 2

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

1. http://www.php.net/manual/en/pdo.construct.php
2. http://php.net/manual/en/pdo.prepared-statements.php
3. How do I insert NULL values 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));

Solution 3

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

$chequeDate = "0000-00-00";

Solution 4

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

Solution 5

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

View more solutions

Comments

[null]

Whats the error message you're getting? Has the database been setup to allow null values? No constraints in place?

[zxc]

use cases.. case when $date1 != null then $date1 else null end

[ajor]

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.

[Anigel]

Echo out the actual query it is running and post them here when it works and fails so we can see what you are actually sending to the db. PS that code is invalid if (empty($date1){ = syntax error

[Gromski]

possible duplicate of Null value is substituted by ''

[ajor]

Ok, so when I restrict the query to just the strings, it works: INSERT INTO contracts (contractor, title) VALUES ("string1","string2") When both dates are not null it works: INSERT INTO contracts (contractor, title, start, end) VALUES ("string1","string2","02/02/02","03/02/02") Otherwise it does not: INSERT INTO contracts (contractor, title, start, end) VALUES ("string1","string2","02/02/02",) INSERT INTO contracts (contractor, title, start, end) VALUES ("string1","string2",,)

[ajor]

@Anigel - sorry that's just a typo here, doesn't exist in my actual code. Edited to change.

[ajor]

@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.

[Himanshu Kumar]

Try this stackoverflow.com/questions/17717349/…

[Wh1T3h4Ck5]

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

[Lenin Zapata]

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

[Martin]

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.