Delete data from cascading table

Asked

Viewed 1,093 times

5

I have some relationships on my tables and I’m willing to delete inversely. inserir a descrição da imagem aqui

on my system the user will have the option to delete a category, here comes my question as I will do this because it should delete the pages category the page descriptions the page images and the image description, I am using PDO and php need some suggestion, with Inner Join it brings only complete data or all related tables populated and with left Join brings repeated data in the edition table for example if I have a category foods and inside I have several pages referencing this category will bring that category repeatedly in editing and believe that this would not be pleasant for the user.

thank you.

php mysql pdo inner
  • 1

    You have foreign keys defined on the Mysql side?

  • If you have foreign keys, you can delete Cache. ex: once the drinks category is removed, all products that are drinks will be removed.

2 answers

4


You can do something like

ALTER TABLE pagina ADD FOREIGN KEY (id_categoria_pagina) REFERENCES categoria_pagina (id_categoria_pagina) ON DELETE CASCADE;
ALTER TABLE descricao_pagina ADD FOREIGN KEY (id_pagina) REFERENCES pagina (id_pagina) ON DELETE CASCADE;
ALTER TABLE imagem ADD FOREIGN KEY (id_pagina) REFERENCES pagina (id_pagina) ON DELETE CASCADE;
ALTER TABLE descricao_imagem ADD FOREIGN KEY (id_imagem) REFERENCES imagem (id_imagem) ON DELETE CASCADE;

Then you don’t need to touch anything on the PHP side - when you delete something the little things go all together.

(I don’t know which program you used to generate this cute image in your question, but it most likely has some functionality to generate these commands I wrote automatically.)

See also

  • then but this will work in mysql?

  • was the mysql workbach

  • Yes; for most things, Mariadb = Mysql.

  • I just found the problem of the system, and more complicated than it seemed, problem with left Join because it has one relationship for many

  • and when it comes to a table with only one data in each I’m able to delete but when for example: I have two images in a table I’m having trouble to bring these two id

  • I am lost. You can edit your question by placing, e.g. the queries you are trying, what they are returning, and what you would like them to return?

  • I managed to solve with left Join, what you think... could improve?

  • now that I understand your answer has become super correct.

Show 3 more comments

0

I was able to develop a method to be able to load the data for deletion, I did it as follows:

public function tableCategoria() {
    $consulta = PDOUtil::getStance()->prepare("SELECT categoria_pagina.id_categoria_pagina as cat_id, categoria_pagina.nome as c_nome,
    pagina.id_pagina as p_id, pagina.tema, pagina.data, pagina.id_categoria_pagina,
    descricao_pagina.id_descricao_pagina, descricao_pagina.sub_titulo, descricao_pagina.texto_da_pagina, descricao_pagina.id_pagina as d_Id_pag,
    imagem.id_imagem, imagem.nome as img_nome, imagem.tipo, imagem.tamanho, imagem.id_pagina as img_id_pagina,
    descricao_imagem.id_descricao_imagem, descricao_imagem.titulo, descricao_imagem.texto_da_imagem, descricao_imagem.id_imagem
    FROM categoria_pagina
    left JOIN pagina ON (pagina.id_categoria_pagina = categoria_pagina.id_categoria_pagina)
    left JOIN descricao_pagina ON (descricao_pagina.id_pagina = pagina.id_pagina)
    left JOIN imagem ON(imagem.id_pagina = pagina.id_pagina)
    left JOIN descricao_imagem ON(descricao_imagem.id_imagem = imagem.id_imagem)
    group by c_nome");
    $consulta->execute();
    while ($linha = $consulta->fetch(PDO::FETCH_OBJ)) {
        echo '<thead>';
        echo '<tr>';
        echo '<td>' . $linha->cat_id . '</td>';
        echo '<td>' . $linha->c_nome . '</td>';

        echo ' <td><a class="btn btn-primary" href="index.php?pagina=alterarCategoria&id_categoria='
        . $linha->cat_id. '&nome_categoria='.$linha->c_nome.'">Editar</a>';
        echo ' <a class="btn btn-danger" id="btn-apagar" href="index.php?pagina=../controller/controllerCategoria&id_categoria='
        . $linha->cat_id . '&nome_categoria='.$linha->c_nome.'&id_imagem='.$linha->id_imagem.'&nome_imagem='.$linha->img_nome.'&id_pagina=' . $linha->p_id .'&acao=deletar">Deletar</a></td>';
        echo '</tr>';
        echo '</thead>';
    }
}

this way I can carry all ids for my deletion.

Browser other questions tagged php mysql pdo inner

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