Failed to register NULL value in database - PHP7

Asked

Viewed 511 times

3

Good night to you all,

I am a beginner and I am developing a registration system for 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

Starting from the form I am sending the NAME of the new category, and select it with the PAI categories. Then 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 the 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 registering manually in phpmyadmin works. If I change the instruction to also works:

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

This clearly states that the fault is in the assignment of the NULL value to the '{$category->getCategoriapai()}' field, but 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?

php mysql mysqli php-7

  • Remove the simple quotes in '{$categoria->getCategoriapai()}', let alone {$categoria->getCategoriapai()}.

  • Francisco removing the simple quotes it presents syntax error; You have an error in your SQL syntax; check the manual that Corresponds to your Mariadb server version for the right syntax to use near ')' at line 1 forgot to comment above

  • What kind of field id? IS INT?

  • Exact idcategory and categorie_cod both are type INT.

  • I get it, the error is because when a value is null, it does not write null. Try to use: {$categoria->getCategoriapai() ?? 'null'}

  • Same error, still presents syntax error: $query = "INSERT INTO category (name, categorie_idcategory) values ('{$category->getNome()}', {$category->getCategoryparent() ?? 'null'}')"; I tried with and without quotation marks, and already test with if ternario also unsuccessfully.

  • You put an extra single quote after the }.

  • I performed a search on this operator and it is still showing syntax error: $query = "INSERT INTO category (name, categorie_idcategory) values ('{$category->getNome()}', {$category->getCategoriapai() ?? 'null'})"; I have tried to change the simple quotes into the instruction unsuccessfully as well. Thank Francis for the support

  • Very strange, asks to print the query, see what is forming.

  • Next, With query: $query = "INSERT INTO category (name, categorie_idcategory) values ('{$category->getNome()}', '{$category->getCategoryparent()} ')"; Before performing the query I print the value of array: Object(Category)#3 (3) { ["idcategory":"Category":private]=> NULL ["name":"Category":private]=> string(5) "test" ["categoriapai":"Category":private]=> NULL } Which certifies that CATEGORIAPAI is null, after executing the query I had the following answer: string(71) "INSERT INTO category (name, categorie_idcategory) values ('test', );

  • There’s no sense what’s going on, when I get home I can run some tests to help you.

  • Yeah, I’m a beginner and I don’t understand what happened, I imagine for those who already program the time. I really appreciate the effort @Francisco

Show 7 more comments

1 answer

1


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 on Ideone.

  • 1

    Exactly @francisco, I believe that directly in the way I was doing was not managing to assign the NULL conditional or INT value, in this way assigning before and passing a variable worked normally. Thanks for the support, very obg

Browser other questions tagged php mysql mysqli php-7

You are not signed in. Login or sign up in order to post.