1
I always come here for research but I never had to ask. I now find myself at a crossroads.
I need to add data in 3 tables at the same time( books, author and editor), and in book, I have foreign key of editor and author.
No sql got something like this:
INSERT INTO autor(nome) VALUES ('testeAutor');
INSERT INTO livros(titulo, edicao, id_autor) values ('nomedolivro','segunda edicao',LAST_INSERT_ID());
INSERT INTO editora(nome) VALUES('testeEditora');
UPDATE livros set id_editora=LAST_INSERT_ID() where titulo='nomedolivro';
In php, I even got something like this and it worked:
$query_autor = "insert into autor (nome) values ('$autor')";
$sql_autor = mysqli_query($con, $query_autor) or die("Não foi possível cadastrar o autor.");
$id_autor = mysqli_insert_id($con);
$query_editora = "insert into editora (nome) values ('$editora')";
$sql_editora = mysqli_query($con, $query_editora) or die("Não foi possível cadastrar a editora.");
$id_editora = mysqli_insert_id($con);
$sql = "insert into livros (titulo, edicao, id_autor, id_editora) values ('$titulo', '$edicao', '$id_autor', '$id_editora')";
echo $sql;
$result = mysqli_query($con, $sql) or die("Não foi possível inserir o novo livro no sistema.");
if(!$result) {
echo "Não foi possivel cadastrar o novo livro";
} else {
echo "Livro cadastrado com sucesso";
echo "<br />";
echo "<a href='cadastro.php'>Voltar para cadastro</a>";
echo "<br />";
echo "<a href='index.php'>Listar todos</a>";
Is there any less "Gambiarra" and/or safer way to do this? After all, it can happen to insert the author, editor and fail the book... I tried Begin Tran, start transaction... Nothing works in xampp. If I put any despair (I searched in google, I looked in mysql doc), it gives that the code is incorrect in the next line after BEGIN or START.
Thanks for your attention, guys!
Update: I managed to get it to work with START TRANSACTION; + COMMIT; via SQL but I wanted to know if there is a more correct or usual way to put this in php. There is a zone like this that I posted on top. Would you have to create one more variable only for START TRANSACTION or have some command in php to do this? I don’t even know exactly how to google this.
examples of transaction here.
– Edilson