Empty DATETIME value stored as 0000-00-00 00:00:00 instead of NULL in MySQL

Question

I have optional date and time form fields which are combined to form a DATETIME-friendly string, which is then sent to the MySQL database. The relevant MySQL column data type is DATETIME, with a default value of NULL. It saves correctly when the date and time fields are completed; however when these optional form fields are empty, the datetime is stored as 0000-00-00 00:00:00 instead of NULL. It needs to be stored as NULL and I have no idea why it isn't. Here's my code:

$Date_Query_Received1   = mysqli_real_escape_string($conn, $_POST['Date_Query_Received']);
$Date_Query_Received2   = implode("-", array_reverse(explode("/", $Date_Query_Received1)));
$Time_Query_Received1   = mysqli_real_escape_string($conn, $_POST['Time_Query_Received']);
$Time_Query_Received2   = $Time_Query_Received1.':00';

if (empty($_POST['Date_Query_Received']) || empty($_POST['Time_Query_Received'])) {
    $Date_Query_Received = NULL;
    $Time_Query_Received = NULL;
} else {
    $Date_Query_Received = $Date_Query_Received2;
    $Time_Query_Received = $Time_Query_Received2
}

$Date_Time_Query_Received = $Date_Query_Received.' '.$Time_Query_Received;

mysqli_query($conn, "INSERT INTO log (Date_Time_Query_Received) VALUES ('$Date_Time_Query_Received')";

The form dates are in DD/MM/YYYY format, hence implode(...array_reverse(...)) to convert to MySQL-friendly DATETIME YYYY-MM-DD format. The same applies with appending ':00' to the time value, as the form field jQuery timepicker is set up for hh:mm only - seconds are not required.

Perhaps it has something to do with the quotes around $Date_Time_Query_Received in the SQL statement; however the query fails without them.

It might be a duplicate question; however the answers do not help me. I already know that MySQL DATETIME accepts NULL values, my problem is that PHP seems to convert null form field entries to empty strings, which then do do not convert to NULL entries in my database, despite the fact that I have set the column to NULL by default. If I'm missing the point, which hopefully I am, please advise. I have tried the example $sql = 'INSERT INTO table (col1, col2) VALUES(' . $col1 . ', ' . (empty($col2) ? NULL : “$col2”). ')'; but the database field still shows 0000-00-00 00:00:00 instead of NULL. — jimbo80, Jan 21 '16 at 09:53

score 4 · Accepted Answer · answered Jan 20 '16 at 20:19

So if you look at your query, you were trying to set the date column to the string ' ' which obviously isn't going to make MySQL happy. It's a very forgiving database though, and defaults to its fallback for a DATETIME column, which is 0000-00-00 00:00:00.

So, how do you pass a null value to the database from PHP? Try using prepared statements like so:

if (empty($_POST['Date_Query_Received']) || empty($_POST['Time_Query_Received'])) {
    $Date_Time_Query_Received = NULL;
} else {
    $Date_Query_Received2 = implode("-", array_reverse(explode("/", $_POST['Date_Query_Received'])));
    $Time_Query_Received2 = "$_POST[Time_Query_Received]:00";
    $Date_Time_Query_Received = "$Date_Query_Received2 $Time_Query_Received2";
}
$stmt = $conn->prepare("INSERT INTO log (Date_Time_Query_Received) VALUES (?)");
$stmt->bind_param("s", $Date_Time_Query_Received);
$stmt->execute();

In addition to making it easier to pass the null value, you're also protecting yourself from SQL injection attacks more effectively than with mysqli_real_escape_string().

Empty DATETIME value stored as 0000-00-00 00:00:00 instead of NULL in MySQL

1 Answers1