0

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.

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

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

0

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
)
  • 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
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.