Failed to enroll NULL value in database – PHP7

Question:

Good evening everyone,

I'm a beginner and I'm developing a system for registering CATEGORIES and SUBCATEGORIES in PHP7 and MYSQL; The logic I used to register Categories and Subcategories is:

Every PARENT CATEGORY is null and every subcategory has a value that references the PARENT CATEGORY.

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

From the form I'm sending the NAME of the new category, and the select with the PAI categories. Then I receive 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 DAO file (data access Layer) I call the method of registering data in the MYSQL database;

//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 register manually in phpmyadmin it works. If I change the instruction to it also works:

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

What clearly says that the failure is in the assignment of the NULL value to the field '{$category->getCategoriapai()}', however when displaying the object with var_dump the CATEGORIAPAI field is set to null.

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?

Answer:

What's happening is that when you ask to print a null value, it doesn't print anything.

You can fix this using the ?? to check if it's 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 on Ideone .

Scroll to Top
AllEscort