Insert in mysql from php saves a date field as 00:00:00 instead of NULL

Question

I receive via POST the following variables, sometimes it must be a date and sometimes it must be null (for example if that event has not happened yet)

$hora_entrada = $_POST['hora_entrada'];
$salida_comida = $_POST['salida_comida'];
$regreso_comida = $_POST['regreso_comida'];
$hora_salida = $_POST['hora_salida'];

so, I assign NULL into the variables if POST is empty:

if ($hora_entrada == '') {$hora_entrada = "NULL";}
if ($salida_comida == '') {$salida_comida = "NULL";}
if ($regreso_comida == '') {$regreso_comida = "NULL";}
if ($hora_salida == '') {$hora_salida = "NULL";}

now, I want to insert the values into a mysql table:

UPDATE  `nomina`.`registro_tiempo` SET  
`hora_entrada` =  '$hora_entrada',
`salida_comida` =  '$salida_comida',
`regreso_comida` =  '$regreso_comida',
`hora_salida` =  '$hora_salida',
WHERE  `registro_tiempo`.`id_tiempo` ='$id_tiempo';
") or die (mysql_error());

the problem is that when the variable is NULL the record says 00:00:00 and I want to keep it NULL

I tried to assign NULL in this two ways without success:

$variable = NULL;

$variable = "NULL";

NOTE: The MySql fields have the NULL value as Predetermined.

¿Do you know any other way to do this? I will apreciate your help

Answer 1

The reason is 'NULL' and NULL are two different things to SQL. You are inserting 'NULL' (the literal string NULL) when you should be inserting NULL (The predetermined NULL value)

UPDATE  `nomina`.`registro_tiempo` SET  
`hora_entrada` =  'NULL',
`salida_comida` =  'NULL',
`regreso_comida` =  'NULL',
`hora_salida` =  'NULL',
WHERE  `registro_tiempo`.`id_tiempo` ='$id_tiempo';
") or die (mysql_error());

It should be

UPDATE  `nomina`.`registro_tiempo` SET  
`hora_entrada` =  NULL,
`salida_comida` =  NULL,
`regreso_comida` =  NULL,
`hora_salida` =  NULL,
WHERE  `registro_tiempo`.`id_tiempo` ='$id_tiempo';
") or die (mysql_error());

To do an easy fix is to put logic to wrap the contents with quotes if it isn't NULL:

<?php
$var = $var != "" ? "'" . $var . "'" : "NULL";
$sql = "INSERT INTO MyTable VALUES ('$id', $var)";
?>

And remember to escape your content (especially if it is provided by the user!) See http://php.net/manual/en/security.database.sql-injection.php

EDIT:

<?php
// If the variable is not null/empty, wrap quotes around it. Otherwise, store as NULL
$hora_entrada = $_POST['hora_entrada'] != "" ? "'" . $_POST['hora_entrada'] . "'" : "NULL";
$salida_comida = $_POST['salida_comida'] != "" ? "'" . $_POST['salida_comida'] . "'" : "NULL";
$regreso_comida = $_POST['regreso_comida'] != "" ? "'" . $_POST['regreso_comida'] . "'" : "NULL";
$hora_salida = $_POST['hora_salida'] != "" ? "'" . $_POST['hora_salida'] . "'" : "NULL";

// You should also mysqli_real_escape_string them
$hora_entrada = mysqli_real_escape_string($hora_entrada);
$salida_comida = mysqli_real_escape_string($salida_comida);
$regreso_comida = mysqli_real_escape_string($regreso_comida);
$hora_salida = mysqli_real_escape_string($hora_salida);

"UPDATE  `nomina`.`registro_tiempo` SET  
`hora_entrada` =  $hora_entrada,
`salida_comida` =  $salida_comida,
`regreso_comida` =  $regreso_comida,
`hora_salida` =  $hora_salida,
WHERE  `registro_tiempo`.`id_tiempo` ='$id_tiempo';
") or die (mysql_error());
?>

Answer 2

If your column is set to allow NULL values, when you want to insert NULL, it cannot be in quotes.

That is, your query looks like this right now:

INSERT INTO `table` (datecol) VALUES('NULL');

but it has to look like:

INSERT INTO `table` (datecol) VALUES(NULL);

You can use:

$variable = "NULL"; // for null values
// and
$variable = "'2012-07-01'"; // for actual date values