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

Navigation
1

I'm editing this question to make it more didactic for other members, since there are only publications addressing this situation outside the community.

I have a INT field with UNIQUE property that can have NULL value.

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

Here is the example that will generate this error: 

$varExemplo = null;
$sql = "INSERT INTO usuario (campoIntExemplo) VALUES ('".$varExemplo."');
$query = $mysqli->query(sql);
    
asked by anonymous 12.09.2017 / 19:14

3 answers

2

I found out how to solve this problem!

When you enter null values from a PHP variable in a database INT field, PHP converts null to "" (empty), which is a STRING value, then that empty value was converted to INT when it reached the database, 0 ( zero ).

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

Here's an example:

// Assigns the value null to the variable $ varExample 

$varExemplo = null;

// Here I created the query that will insert the value of the variable $ varExample in the InstName field . NOTE: The query will also insert null in the primary field Pk field , as this field is auto_increment , it will generate the registration ID automatically. p> 

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


$idUsuario = mysqli->insert_id;

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

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

Thank you very much for @rray's efforts to help me solve the problem.

    
15.09.2017 / 19:42
2

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

This is just an example. It is very important to use prepared statements or to correctly sanitor and escape the values sent by the user 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 outputs: 

INSERT INTO (c1, c2, c3) values('v1', null, 'v3')
INSERT INTO (c1, c2, c3) VALUES('v1', 'novo valor', 'v3')
    
12.09.2017 / 20:02
1

I believe this is possible, yes. In this answer 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)
);

    
12.09.2017 / 19:19
How to calculate the factorial of a number? How to store Memo contents in string variable in Delphi?