insert null date / time into mysql with php causing 0000-00-00 / 00:00:00

Question

i see a couple of questions on here and elsewhere that address this in some cases, but not quite what i need.

i am working with a script that stores an array of values to do either an insert or update based on changes.

some dates/times are set and some are null. i am trying with $var=NULL, then inserting with insert into ... ('{$var}') and getting 00:00:00 for the time if null and the time if time is set.

inserting with $var="NULL" and insert into ... ('{$var}') and getting 00:00:00 if null and the time if time is set.

if i remove the quotes for the insert ... ({$var}) it works if null, but if the date is not null, it of course fails.

i have another VARCHAR field i am doing the same thing with and it works fine. i.e. if there is a string it is passed in as a string and if it's empty i am setting that var=null. i am using quotes on the insert query and if it's null, it goes in as null and if not null, the string inserts.

using a conditional statement is going to require a pretty big rewrite, so i am hoping to avoid that.

any suggestions on how to make this work without IF statements (if possible), would help out.

thanks.

i have tested this several ways and i am not able to get the desired results without making the date/time fields VARCHAR, which i don't want to do. here is the db structure, insert queries and results.

id  int(11)         No  None    AUTO_INCREMENT                          
event_new_date_start    date            Yes NULL                                
event_new_time_start    time            Yes NULL                                
event_link  varchar(150)    latin1_swedish_ci       Yes NULL    




    $event_new_date_start1 = 'NULL';
    $event_new_time_start1 = 'NULL';
    $event_link1 = 'NULL';
    $event_new_date_start2 = '2011-04-04';
    $event_new_time_start2 = '13:13';
    $event_link2 = 'http://abc.com';
    $event_new_date_start3 = NULL;
    $event_new_time_start3 = NULL;
    $event_link3 = NULL;

    mysql_query("
    insert into test_dates (event_new_date_start, event_new_time_start, event_link) values
    ('{$event_new_date_start1}', '{$event_new_time_start1}', '{$event_link1}')
    ");

    mysql_query("
    insert into test_dates (event_new_date_start, event_new_time_start, event_link) values
    ('{$event_new_date_start2}', '{$event_new_time_start2}', '{$event_link2}')
    ");

    mysql_query("
    insert into test_dates (event_new_date_start, event_new_time_start, event_link) values
    ('{$event_new_date_start3}', '{$event_new_time_start3}', '{$event_link3}')
    ");                          


        1   0000-00-00  00:00:00    NULL
        2   2011-04-04  13:13:00    http://abc.com
        3   0000-00-00  00:00:00

when i changed the date/times fields to CHAR, it worked as expected using quotes in the query.

        4   NULL    NULL    NULL
        5   2011-04-04  13:13   http://abc.com
        6

rid · Accepted Answer · 2011-05-22T01:41:05.120

5

If you're using quotes, then you're saying that you want the date to be the string 'NULL'. MySQL cannot parse that to a valid date, therefore it falls back to 0.

When setting the value of $var, set it to either just NULL or a quoted string if it's not null, then simply use ({$var}) in the query instead of ('{$var}').

edited May 22 '11 at 01:41

answered May 22 '11 at 01:34

rid

61,078
31
152
193

i tested this as well, unfortunately some dates are null and some are not, without quotes, the non-null dates go in correctly, but the nulls are 0s. with no quotes, non-null dates throw errors. i am guessing the only way to solve this is to do IFs to see if the value is null or not and create the query dynamically. – Jazzy May 23 '11 at 01:08
Your life will be easier if you use PDO and prepared statements; it will put in NULLs as NULL and dates wrapped in quotes. It'll also make SQL injection prevention much easier. – El Yobo May 23 '11 at 05:35
@Jason, `if`s, but in PHP, not in SQL. I mean, `if (empty($var)) { $var = 'NULL'; } else { $var = "'" . mysql_real_escape_string($var) . "'"; }`. You can do this for any `$var`, not just the date, so you can do it once. MySQL doesn't complain even if you insert integers between apostrophes (`'123'`), so you can use this everywhere really. – rid May 23 '11 at 11:19
this is the best answer, but it didn't really solve the problem. i am going to go out on a limb and say this might be a bug in mysql. not sure. BUT, i changed it to use PDO and the problem was solved. thanks el yobo. – Jazzy May 24 '11 at 00:03

score 1 · Answer 2 · edited May 23 '17 at 12:19

rdineiu is right, the problem is that you are inserting the word "null". MySQL doesn't understand that and so instead of giving an error (like many DBs would), it inserts a time of all zeros. This is especially fun in Java since trying to read that value will cause an exception to be thrown.

The best thing to do is to move to parameterized SQL queries using DBO. You can find a little more and some links with this question and answer on SO. This will not only solve your problem, but it will solve (some) SQL injection problems that could occur with other values being inserted and read back.

Without doing that (which, again, you should and is the correct answer), you could do the insert without the quotes ("({$var})"), and run the date through a function that appends the necessary quotes if it isn't null. But this won't protect you if you get date values directly from users, and you'll have this same problem if you have a string field you want to be nullable.

score 0 · Answer 3 · answered May 23 '11 at 02:03

MySQL often does completely stupid stuff like this; see this useful list of MySQL "gotchas". In this case, I expect that your date field is NOT NULL and your SQL compliance mode is not very strict; this causes MySQL to enter a default date of 0 instead of the NULL that you actually asked it to do. Change your schema to have your date field DEFAULT NULL and this should go away.

Note that this will work if you make the change that the others suggested, e.g. insert NULL not a quoted 'NULL'.

the default is NULL for the date fields and if you see the above code, it inserts '' if the var is set to NULL and 0s if the var is set to 'NULL'. thanks for the link though. — Jazzy, May 23 '11 at 18:17

insert null date / time into mysql with php causing 0000-00-00 / 00:00:00

3 Answers3