database – SQL Query to get post_id from wp_posts and and meta_key(s) from wp_postmeta


I have a custom post type called media_coverage and need to export via PHPMyAdmin some of the data in that post type that is in wp_posts (post_title) and also in wp_postmeta (meta_key).

I obviously need to use the post_id, but I don’t know how to dynamically pass the post_id in the query to get the data from both the wp_posts and wp_postmeta tables.

And, I don’t know how to get multiple meta_key values at the same time from wp_postmeta.

What I’ve worked through in PHPMyAdmin:

• This query shows me all the posts and post_id’s, etc, for the media_coverage custom post type:

SELECT* FROM wp_posts WHERE post_type="media_coverage"

• The post_title is one bit of data I need to retrieve; this shows me all the post titles of all the media_coverage posts from wp_posts:

SELECT post_title FROM wp_posts WHERE post_type="media_coverage"

• But what I need to do is also retrieve multiple meta_key values from wp_postmeta; this gives me one of the meta_keys called coverage_url:

SELECT* FROM wp_postmeta WHERE post_id = '82080' AND meta_key = 'coverage_url'

But that’s only from post ID 82080, as I don’t know how to have the query use all of the post_id’s of available media_coverage posts.

So how do I construct a query like this:

SELECT* FROM wp_posts WHERE post_type=”media_coverage” AND

(pseudo code)

the post_title from that post_id in wp_posts

and the meta_key = ‘source_name’ from that post_id in wp_postmeta

and the meta_key = ‘coverage_url’ from that post_id in wp_postmeta

BlueDogRanch 2 weeks 2022-09-08T15:00:31-05:00 0 Answers 0 views 0

Leave an answer