Search code examples
phpmysqldatabasemyisam

Deleting related records in MySQL

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?

Solution 
  • 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.