Joining WordPress Comments with Post Titles from MySQL DB

Posted on

Question :

I’m working with a wordpress database and need to get some data on the comments.

The goal is to get comments from a decommissioned site. I have the database running on my local system and can generate a report from the wp_comments table easy enough. That table has a column named ‘comment_post_id’ which is a direct correlation to the ID column in the wp_posts table.

I’ve dumped both from mysql into excel and done a vlookup from the comments table into the posts table to get the desired report.

While the dump->excel process works, I know this could probably be done with a query in mysql.

My first crack at it fails:

SELECT * FROM 'wp_posts' WHERE ('wp_comments.comment_post_id' = 'wp_posts.id');

and doesn’t even include a join yet.

Can you provide any pointers or tips on getting a correct query?

Thank you.

Answer :

SELECT * FROM wp_posts WHERE id IN (SELECT comment_post_id FROM wp_comments);

or

SELECT A.* FROM wp_posts A INNER JOIN
(SELECT comment_post_id FROM wp_comments) B
ON A.id = B.comment_post_id;

Leave a Reply

Your email address will not be published. Required fields are marked *