Deleting from multiple tables mysql with php

Question

I have literally tried everything i've found on the internet about deleting from multiple tables in same query, but it just ends up deleting from the first table(Posts)..

So how does one do this the correct way with php and pdo?

examples of what i've tried=

$dsn = "DELETE FROM Posts, Comments USING Posts, Comments WHERE Posts.ID = Comments.PostID  AND Comments.PostID=:my var";

$dsn = "DELETE FROM Posts LEFT JOIN Comments ON `Comments.PostID` = `Posts.ID` WHERE `Posts.ID`=:tit";

$dsn = "DELETE Posts , Comments  FROM Posts  INNER JOIN Comments  WHERE Posts.ID = Comments.PostID and Posts.ID =:myvar";

The tables looks as following:

 TABLE: Posts

    ID(PK AI) 
    Title(VARCHAR)
    Post(VARCHAR)
    Author(VARCHAR)
    Date(DATETIME)



TABLE: Comments

ID(PK AI so all comments get unique id's)
Name(VARCHAR)
Comment(VARCHAR)
Date(DATETIME
PostID(INT)

If you define `Coments.PostID` as FK with `ON DELETE CASCADE`, you will just need to delete from `Posts`. — Paul Spiegel, Feb 17 '17 at 23:17
@PaulSpiegel that sounds intressting, just need to find out how to do that then :) — per källström, Feb 17 '17 at 23:31
Look at the first example here: https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html — Paul Spiegel, Feb 17 '17 at 23:33
MCVE stands for Minimal, Complete, and Verifiable example. http://stackoverflow.com/help/mcve. — Louis Loudog Trottier, Feb 17 '17 at 23:54

score 1 · Accepted Answer · answered Feb 18 '17 at 00:06

1

You have different ways to solve this:
1- delete with inner join

$dsn = "DELETE Posts.*, Comments.*  FROM Posts  INNER JOIN Comments  WHERE Posts.ID = Comments.PostID and Posts.ID =:myvar";

2- delete cascade, drop if exist foreing key and execute this:

ALTER TABLE Comments
  ADD CONSTRAINT fk_postid 
  FOREIGN KEY (PostID) 
  REFERENCES Posts(ID) 
  ON DELETE CASCADE;

3- using trigger after delete in the table Posts

  delimiter $$
    CREATE TRIGGER `after_delete_Posts`     
      AFTER DELETE ON `Posts`     
      FOR EACH ROW     
    BEGIN
      DELETE FROM Comments where PostID = OLD.id;
    END
    $$
    delimiter ;

answered Feb 18 '17 at 00:06

Danilo Bustos

1,083
1
7
9

I tried with the cascade, i dropped the FK and AI on ID in comments then ran your query and got this: #1452 - Cannot add or update a child row: a foreign key constraint fails (`pelle`.`#sql-c32d_67e16`, CONSTRAINT `fk_postid` FOREIGN KEY (`PostID`) REFERENCES `Posts` (`ID`) ON DELETE CASCADE) i have tried the delete query before, it doesnt work im afraid – per källström Feb 18 '17 at 00:21
The definition of the data type of both postID and ID are the same? – Danilo Bustos Feb 18 '17 at 00:28
before alter table add this:`SET FOREIGN_KEY_CHECKS=0;` ,and after add `SET FOREIGN_KEY_CHECKS=1;` – Danilo Bustos Feb 18 '17 at 00:35

Deleting from multiple tables mysql with php

1 Answers1