How to convert 0 to null (Error inserting NULL into an INT field from a variable)

Question

I am editing this question to be more didactic for other members, since there are only publications addressing this situation outside the community en.

I own a field INT property UNIQUE that may have value NULL.

The value that will be inserted in this field comes from a variable that may have value NULL. The problem is that by doing the INSERT, if this variable has a null value, the value 0 (zero) in this field, which will cause problems when entering a new record thanks to the property UNIQUE.

Follow the example that will generate this error:

$varExemplo = null;
$sql = "INSERT INTO usuario (campoIntExemplo) VALUES ('".$varExemplo."');
$query = $mysqli->query(sql);

Answer 1

You can use a ternary to properly format SQL if there is a value that gives the variable its own value plus simple quotes (in the case of a varchar for numeric types do not add them) otherwise return the null literal.

That’s just one example. It is very important to use Prepared statements or correct healing and escaping user-submitted values to avoid problems with sql Injection.

$inputVazio = empty($_POST['inputVazio']) ? 'null' : (int) $_POST['inputVazio'];
$sql = "INSERT INTO (c1, c1, c3) VALUES('v1', $inputVazio, 'v3')";

Possible exits:

INSERT INTO (c1, c2, c3) values('v1', null, 'v3')
INSERT INTO (c1, c2, c3) VALUES('v1', 'novo valor', 'v3')

Answer 2

I figured out how to solve this problem!

When null values are inserted from a variable PHP in a field INT of the database, the PHP converts null to "" (empty), which is a value STRING, then this empty value was converted to INT when it arrived at the database, generating the value 0 (zero).

The solution was to give a UPDATE after the INSERT using the last id inserted as a parameter for the WHERE.

Following example:

//Assigns the value null to the variable $man

$varExemplo = null;

//Here I created the query that will insert the value of the $varEmplo variable in startEmployment. NOTE: The query will also insert null in the primary field campoPk, as this field is auto_increment, it will generate the record ID automatically.

$sql = "INSERT INTO usuario (campoPk, campoIntExemplo) VALUES (null,'".$varExemplo."');
$query = $mysqli->query(sql);

//With the constant insert_id i take the generated ID for the record we generate with INSERT, and assign this id to the variable $user.

$idUsuario = mysqli->insert_id;

//Finally I make a update using the value of $usuarioId as parameter.

$sql = "UPDATE usuario SET id_visamg = null WHERE id_visamg= 0 and id_usuario = $idUsuario";
$query = $mysqli->query($sql)

Thank you so much for all of @rray’s efforts to help me solve the problem.

Answer 3

I believe that’s possible, yes. In that reply has something like what you want:

INSERT INTO etc(campo1, campo2)
VALUES
(
   (CASE campo1 WHEN '' THEN NULL ELSE campo1 END),
   (CASE campo2 WHEN '' THEN NULL ELSE campo2 END)
);

Note: the example got ugly because you did not give many details of table/ fields, but just adapt.