Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I'm trying to delete all comments from posts in a certain category in a Wordpress database.

The Wordpress database is rather complicated so this must be probably some join of wp_posts, wp_comments and wp_term_relationships as the categories are not stored with neither posts nor comments.

How can I do this? I'm struggling to come up with a query.

share|improve this question
add comment

migrated from stackoverflow.com Jan 7 '13 at 15:35

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 0 down vote accepted

The wordpress database seems fairly straight forward and is extremely well documented:

http://codex.wordpress.org/Database_Description

It seems to me that all the category information is stored in the table wp_term_relationships. So you should select from the comments table, join it with the posts table and then the term_relationships table in order to get the category for each comment.

Something like:

SELECT *
FROM wp_comments
WHERE comment_post_ID
IN (

SELECT c.object_id
FROM wp_terms a, wp_term_taxonomy b, wp_term_relationships c
WHERE a.name = 'my_category_slug'
AND b.term_id = a.term_id
AND b.taxonomy = 'category'
AND c.term_taxonomy_id = b.term_taxonomy_id
)
share|improve this answer
    
i found this code on wordpress forums too but it doesnt work well, same problem, it lists comments from many different categories... –  user1394013 Jan 6 '13 at 21:03
    
ok the problem here is its not category slug isntead of "'my_category_slug'" but actual name, which is case sensitive. –  user1394013 Jan 6 '13 at 21:07
add comment

I can provide you the delete command from join tables. Then you need to add the relevant tables and columns though

-- using LEFT JOIN

DELETE  x.*
FROM    table1 AS x 
LEFT JOIN
        table2 AS y 
ON      y.id = x.id

-- using IN:

DELETE  
FROM    table1 AS x
WHERE   id IN
        (
        SELECT  id
        FROM    table2
        )

Usually left join is faster than in. Looking at that ERD, you would also better watch out for any foreign key relationships if delete on cascade is not implemented.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.