Delete cascading table data

Navigation
5

I have some relationships in my tables and I'm wanting to delete them in reverse.

In my system the user will have the option of deleting a category, here comes my doubt as I will do this, because it should delete the pages, category descriptions of the page, the images of the page and the description of the image, I am using PDO and php I need some suggestions, with inner join it brings only complete data ie all populated related tables and with left join brings repeated data in the edit table eg if I have a food category and inside I have several pages referencing that category will go bring that category over and over again in the edit and I believe that would not be pleasant for the user.

Thank you.

    
asked by anonymous 13.06.2015 / 20:35

2 answers

4

You can do something like this 

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 do not have to mess around with the PHP side - when you delete something the pendants will all go together.

(I do not know which program you used to generate this cute image in your question, but it most likely has some functionality to generate those commands that I wrote automatically.)

See also

13.06.2015 / 21:07
0

I was able to develop a method to load the data for deletion, I did 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 load all the ids for my deletion.

    
13.06.2015 / 21:54
Receive vector size What is and how does an Enterprise Application Bus (ESB) work?