Failed to register NULL value in database - PHP7

Question

Failed to register NULL value in database - PHP7

Navigation

#1 by (0 votes)

2

Good night everyone,

I'm a beginner and I'm developing a CATEGORY and SUBCATEGORY registration system in PHP7 and MYSQL; The logic I used to register for Categories and Subcategories is:

All PAI CATEGORY is null and any subcategory has a value that refers to CATEGORY PAI.

EX:
id|Nome |categoria_cod
1 |Carro|NULL --> categoria pai
2 |Fusca|1 --> Subcategoria

Starting from the form I am sending the NAME of the new category, and the select with the categories PAI. Then I get on a registration page:

$categoria = new Categoria();
$categoria->setNome(filter_input(INPUT_POST, "nome", 
FILTER_SANITIZE_STRING));

//Aqui está a validação, se categoriapai possui ou não um valor, se nao 
possui atribui NULO
if (filter_input(INPUT_POST, "categoriapai", FILTER_SANITIZE_NUMBER_INT)) {
$categoria->setCategoriapai(filter_input(INPUT_POST, "categoriapai", 
FILTER_SANITIZE_NUMBER_INT));
} else {
$categoria->setCategoriapai(NULL);
}

$categoriaDAO = new categoriaDAO($conexao);

//Se cadastrou retorna msg de sucesso ou falha
if ($categoriaDAO->cadastrarCategoria($categoria)) {
?>
<div class="alert alert-success" role="alert">Categoria cadastrada com 
sucesso</div>
<?php
die();

In the Data Access Layer (DAO) file I call the MYSQL database data method;

//Categoria-PAI = nulo, se possui algum valor é categoria filho
function cadastrarCategoria($categoria) {
    $query = "INSERT INTO categoria (nome, categoria_idcategoria) values 
('{$categoria->getNome()}', '{$categoria->getCategoriapai()}')";
    return mysqli_query($this->conexao, $query);
}

My problem is exactly in the INSERT query, if I manually register in phpmyadmin it works. If I change the statement to also works:

"INSERT INTO categoria (nome, categoria_idcategoria) values ('{$categoria->getNome()}', NULL)";

This clearly states that the failure is in assigning the NULL value to the field '{$ category-> getCategoriapai ()}', but when displaying the object with var_dump the CATEGORYPAI field is set to null. p>

object(Categoria)#3 (3) { ["idcategoria":"Categoria":private]=> NULL ["nome":"Categoria":private]=> string(5) "teste" ["categoriapai":"Categoria":private]=> NULL }

The mysql error is this:

Cannot add or update a child row: a foreign key constraint fails ('acheimeuequipo'.'categoria', CONSTRAINT 'fk_categoria_categoria1' FOREIGN KEY ('categoria_idcategoria') REFERENCES 'categoria' ('idcategoria') ON DELETE NO ACTION ON UPDATE NO ACTION)

How can I change this query to receive and insert both NULL or 2 values?

php mysql php-7 mysqli

asked by anonymous 13.11.2017 / 00:13

1 answer

Filter database by checkbox Log4net has stopped showing the logs

score 0 · Accepted Answer

What is happening is that when you ask to print a null value, it does not print anything.

You can fix this by using the ?? operator to check if it is null:

function cadastrarCategoria($categoria) {
    $categoriaPai = $categoria->CategoriaPai ?? 'null';
    $query = "INSERT INTO categoria (nome, categoria_idcategoria) values ('{$categoria->Nome}', {$categoriaPai})";
    return mysqli_query($query);
}

See working at Ideone .