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
– Paul Spiegel, Commented Feb 17, 2017 at 23:17
@PaulSpiegel that sounds intressting, just need to find out how to do that then :) — per källström
– per källström, Commented Feb 17, 2017 at 23:31
Look at the first example here: dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html — Paul Spiegel
– Paul Spiegel, Commented Feb 17, 2017 at 23:33

Danilo Bustos · Accepted Answer · 2017-02-18 00:06:43Z

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, 2017 at 0:06

Danilo Bustos

1,0931 gold badge7 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

per källström Over a year ago

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

Danilo Bustos Over a year ago

The definition of the data type of both postID and ID are the same?

Danilo Bustos Over a year ago

before alter table add this:SET FOREIGN_KEY_CHECKS=0; ,and after add SET FOREIGN_KEY_CHECKS=1;

Collectives™ on Stack Overflow

Deleting from multiple tables mysql with php

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related