PHP empty string error for mysql date column with null value

Question

I have an HTML form with an input field (type="date") and a mysql DB with a column with datatype "date" that accepts null when I do the query directly in phpmyadmin.

Also when I select a date in my html form (using browser default date picker) the query runs correctly. But when I leave the mentioned input blank, the following error is shown "Incorrect date value: '' for column 'received_date'.

Any help to get rid of this issue is appreciated.

Here are the sql and DB/Table schema:

CREATE TABLE customer.tbl1 ( id INT NOT NULL AUTO_INCREMENT , usrname
VARCHAR(32) NOT NULL , received_date DATE NULL DEFAULT NULL , PRIMARY
KEY (id)) ENGINE = MyISAM;

$sql = "INSERT INTO tbl1 (usrname, received_date) VALUES ('$usrname',
'$received_date')";

check if (empty($_post['date']))? NULL: $_POST['date'] and then do query — Death-is-the-real-truth
– Death-is-the-real-truth, Commented Apr 2, 2018 at 10:39
Can you share the SQL query you're using along with the schema from that table? — dantheman2865
– dantheman2865, Commented Apr 2, 2018 at 10:40
In my query I used variables like INSERT INTO TBL1 (username, received_date) VALUES ('$usrname', '$received_date')... I think the single quotes that I use to surround the variable causes the problem. Because I replaced the variable with NULL (without quotes). It worked. But I cannot use the NULL in my query permanently because the date field is optional that may or may not have date selected. — AWN
– AWN, Commented Apr 2, 2018 at 10:44
in question, you are talking about SELECT and here you are providing INSERT. very confusing. Please add HTML+CODE in your question to clarify your problem. — Death-is-the-real-truth
– Death-is-the-real-truth, Commented Apr 2, 2018 at 10:47
With select I meant choosing date from the browser default date-time picker. Because in the HTML form the input type is date. — AWN
– AWN, Commented Apr 2, 2018 at 10:49

AWN · Accepted Answer · 2018-04-03 11:03:20Z

2

Thank you all. The following solved the issue:

if (empty($received_date)) {
            $received_date= 'NULL';
}

However, before posting the problem here I tried this (if condition) with NULL without the single quotes but It didn't work. Now the single quotes solved the issue.

answered Apr 3, 2018 at 11:03

AWN

691 silver badge11 bronze badges

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

1 Comment

webs Over a year ago

used the same but in my case it was NULL without quotes.

dantheman2865 · Accepted Answer · 2018-04-02 23:55:29Z

1

The answer depends on your application: Is Null a valid value for your data?

If that's ok, then update your database schema to allow Null values (https://stackoverflow.com/a/35963275/3341745).

If you don't want this value to be Null, then the database check is doing its job. In this case, you should validate the data on the web page before the user is allowed to submit it (https://www.w3schools.com/js/js_validation.asp).

answered Apr 2, 2018 at 23:55

dantheman2865

3351 silver badge14 bronze badges

Collectives™ on Stack Overflow

PHP empty string error for mysql date column with null value

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related