0

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.

0
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;
1
  • Thanks for your direction! I flipped the wp_posts and wp_comments in the SELECT statements to get better visibility of the info I needed from the comments. Here's the query I came up with: SELECT A.comment_ID, A.comment_post_id, B.id, B.post_title, A.comment_author, A.comment_author_email, A.comment_author_url, A.comment_date, A.comment_content, A.comment_approved, A.comment_type FROM wp_comments A INNER JOIN (SELECT id, post_title FROM wp_posts) B on A.comment_post_ID = B.id WHERE A.comment_approved NOT LIKE 'spam';
    – Michael J
    Feb 4 '15 at 6:14

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.