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?
Remove the simple quotes in
'{$categoria->getCategoriapai()}'
, let alone{$categoria->getCategoriapai()}
.– Francisco
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
– Helder Ferrari
What kind of field
id
? ISINT
?– Francisco
Exact idcategory and categorie_cod both are type INT.
– Helder Ferrari
I get it, the error is because when a value is null, it does not write
null
. Try to use:{$categoria->getCategoriapai() ?? 'null'}
– Francisco
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.
– Helder Ferrari
You put an extra single quote after the
}
.– Francisco
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
– Helder Ferrari
Very strange, asks to print the query, see what is forming.
– Francisco
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', );
– Helder Ferrari
There’s no sense what’s going on, when I get home I can run some tests to help you.
– Francisco
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
– Helder Ferrari