Deleting rows from 3 tables at once in PHP

Question

Short

My tables structure looks like that

enter image description here

And here is Sql statements

            $stmt = $this->db->prepare("DELETE FROM chapters WHERE (subject_id=? AND id = ?)") or die($this->db->error());
            $stmt->bind_param("ii", $subject_id, $node);
            $stmt->execute();
            $stmt = $this->db->prepare("DELETE FROM sections WHERE (subject_id=? AND chapter_id=? )") or die($this->db->error());
            $stmt->bind_param("ii", $subject_id, $node);
            $stmt->execute();
            $stmt = $this->db->prepare("DELETE FROM paragraphs WHERE (subject_id=? AND chapter_id=?)") or die($this->db->error());
            $stmt->bind_param("ii", $subject_id, $node);
            $stmt->execute();

So what I want to do is, to merge this 3 statements into one and optimize server load.

Detailed

For ex., if I want to delete row with id=1 from chapters table, then also delete from 2 more tables: sections, paragraphsby 2 parameters: $node and $subject_id (Of course, If there is rows with those parameters. I mean there must be join to prevent any error).

Question is..

Is that possible? I can't figure out, how sql statement must look like. Any suggestions?

score 6 · Accepted Answer · edited May 23 '17 at 10:32

6

If you have set up foreign key constraints with ON DELETE CASCADE then you only need to delete the parent row. MySQL will then delete the child rows automatically.

How do I use on delete cascade in mysql?

edited May 23 '17 at 10:32

Community

1
1

answered May 03 '12 at 19:49

Mark Byers

811,555
193
1,581
1,452

2

@TuralTeyyuboglu: I think you have the parent-child relationship reversed. The key is often defined on a column called `sometable_id` and refers to a column `id` in `sometable`. It seems that you have it the other way around. PS: I'm not interested in offering dedicated remote support for free, sorry! – Mark Byers May 03 '12 at 20:12

score 4 · Answer 2 · answered May 03 '12 at 20:12

I haven't tried it, but you could try multi-table deletes:

http://dev.mysql.com/doc/refman/5.6/en/delete.html#id933512

^{DELETE chapters, sections, paragraphs
    FROM      chapters
    LEFT JOIN sections   ON   sections.subject_id = $subject_id AND   sections.chapter_id = $node
    LEFT JOIN paragraphs ON paragraphs.subject_id = $subject_id AND paragraphs.chapter_id = $node
    WHERE                     chapters.subject_id = $subject_id AND chapters.id           = $node}

I'm not sure if using left joins is really faster than using 3 separate deletes.

Deleting rows from 3 tables at once in PHP

Short

Detailed

Question is..

2 Answers2