Deleting related records in MySQL

Question

I have two MySQL (MyISAM) tables:

Posts: PostID(primary key), post_text, post_date, etc. 

Comments: CommentID(primary key), comment_text, comment_date, etc.

I want to delete all the comments in the "Comments" table belonging to a particular post, when the corresponding post record is deleted from the "Posts" table.

I know this can be achieved using cascaded delete with InnoDB (by setting up foreign keys). But how would I do it in MyISAM using PHP?

Answer 1

DELETE
    Posts,
    Comments
FROM Posts
INNER JOIN Comments ON
    Posts.PostID = Comments.PostID
WHERE Posts.PostID = $post_id;

Assuming your Comments table has a field PostID, which designates the Post to which a Comment belongs to.

Answer 2

Even without enforceable foreign keys, the method to do the deletion is still the same. Assuming you have a column like post_id in your Comments table

DELETE FROM Comments
 WHERE post_id = [Whatever Id];

DELETE FROM Posts
 WHERE PostID = [Whatever Id];

What you really lose with MyISAM is the ability to execute these two queries within a transaction.

Answer 3

I've never tried it, but you could set up a trigger to do cascading deletes (if you are using >=5.0)

DELIMITER $$
CREATE TRIGGER Posts_AD AFTER DELETE ON Posts
FOR EACH ROW
BEGIN
  DELETE FROM Comments WHERE post_id = OLD.PostID;
END $$
DELIMITER ;