CODEWITHSUNDEEP
phpmysqldatepdo
marmita
marmita

Reputation: 11

How to insert empty string into a date column using PDO?

I am using a PDO prepared statement to insert values. One of the values is a date, and sometimes it can be empty. In MySQL schema, the date column is set to allow NULL values.

Let's assume date_column is a date and it allows NULL. When I do this:

$query = "INSERT INTO tbl(date_column) VALUES(?)";
$stmt = $pdo->prepare($query);
$stmt->execute(['']);

This is giving me this error:

SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect date value: '' for column 'date_column' at row 1

In phpMyAdmin, I can execute this query without errors. It sets the date to 0000-00-00

INSERT INTO tbl(date_column) VALUES('')

Isn't that the same query that is executed by PDO behind the scenes in the code example above? What's wrong?

All I want is to be able to insert empty string '' as a date without errors. I don't care if it is set to 0000-00-00

Upvotes: 1

Views: 1949

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

This is too long for a comment.

This is a fundamental problem:

All I want is to be able to insert empty string '' as a date.

Strings are strings, and dates are dates. You should not want to insert an empty string into the column because that makes no sense. Instead, you should want to insert either a NULL value or the default value.

So, your code should look like:

INSERT INTO tbl(date_column)
    VALUES(NULL)

or:

INSERT INTO tbl(date_column)
    VALUES(DEFAULT)

The fact that MySQL does better type checking on prepared queries with parameters is actually a good thing.

Upvotes: 1

nospor
nospor

Reputation: 4220

Empty string is not a null... NULL is a NULL

$stmt->execute([null]);

Upvotes: 0

Related Questions