PHP MYQSL single insert statement to enter either NULL, or time as datetime value when INSERT statement uses variable?

Question

Apologies up front: Self taught so sometimes I miss something really basic...

I have a JS application where users can enter a time in a form and can revisit the form and edit the time later. The entry is sent to a MYSQL DB using PHP and stored in a TIME-type column. There will be cases where a user will need to enter midnight, and other cases where a user will need to intentionally leave the time blank (so it cannot display as midnight), so it is important that the time display as "-:--" in the form's input field if it has yet to be entered, but as "12:00" if the user actually intends for the entry to be midnight. As long as the value stores in the DB as NULL it works fine.

The problem is that if I simply allow the value to pass from JS to PHP and then insert as the variable '$TIME_ENTRY' when sending from PHP, a 'null' from js is interpreted as '12:00' on insert (Seems to be driven by the fact of single quotes around the variable may be causing it to read as 0?).

My current workaround is simply to test for null and have two separate insert statements:

if null{ "INSERT INTO `TBL` (`TIMECOLUMN`) VALUES (NULL)"}
else { "INSERT INTO `TBL` (`TIMECOLUMN`) VALUES ('$TIME_ENTRY')"

but I have to believe it can be done in a single statement. Can anyone help with this?

FROM JS:

var t = $("#time_entry_field").val();
var request = $.ajax({
    url: "PHPFile.php",
    type: "POST",
    async: true,
    data: "Time=" + t,
    success: function(){
         ...etc
    }
})

IN "PHPFile.php":

$TIME_ENTRY = $_POST['Time'];
$conn->query("INSERT INTO `TBL` (`TIMECOLUMN`) VALUES ('$TIME_ENTRY')");

The above will store the value as '12:00:00' in my DB on receiving an empty string from $("#time_entry_field").val().

However:

$TIME_ENTRY = $_POST['Time'];
$conn->query("INSERT INTO `TBL` (`TIMECOLUMN`) VALUES (NULL)");

works as expected and stores the value as NULL.

Is there some simple way to use a single insert statement without getting hungup on an empty string from JS reading as a 0 in php? I have to do a lot of different versions of this so it seems super wasteful to write a million if statements...

Answer 1

If you want to insert NULL instead of an empty string, you can use the NULLIF() function. As mentioned in the comments, you should also use a prepared statement to avoid SQL-injection.

$stmt = $conn->prepare("INSERT INTO `TBL` (`TIMECOLUMN`) VALUES (NULLIF(?, ''))");
$stmt->bind_param("s", $TIME_ENTRY);
$stmt->execute();