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
post_title) and also in associated
I obviously need to use the post_id, but I don't know how to 1) dynamically pass the
post_id in the query to get the data from the
wp_posts table and the post_id associated data from the
And, 2) I don't know how to get multiple
meta_key values at the same time from
This is 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'
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
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
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
So how do I construct a query like this:
SELECT* FROM wp_posts WHERE post_type = 'media_coverage' AND
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
This query works somewhat; I get the columns of
source_name, but I also get all the columns of each post, like draft status, date published, revisions, etc., so limiting the output to
source_name would be nice.
SELECT wp_posts.*, a.meta_value source_name, b.meta_value coverage_url FROM wp_posts LEFT JOIN wp_postmeta a ON wp_posts.ID = a.post_ID AND a.meta_key='source_name' LEFT JOIN wp_postmeta b ON wp_posts.ID = b.post_ID AND b.meta_key='coverage_url' WHERE wp_posts.post_type = 'media_coverage';