0

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 associated wp_postmeta (meta_key) keys.

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 wp_postmeta tables.

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

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'

• 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

Edit 9/14/22

This query works somewhat; I get the columns of coverage_url and source_name, but I also get all the columns of each post, like draft status, date published, revisions, etc., so limiting the output to post_id, coverage_url and 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';
3
  • This is a question about SQL, not WordPress. But you need to use a JOIN statement.
    – bosco
    Sep 12, 2022 at 14:36
  • This is a question about SQL and WordPress. So how do I use a JOIN statement? Sep 12, 2022 at 16:13
  • 1
    The question is about the SQL language. It's in the context of WordPress in that the circumstantial table names and columns are WordPress-related, but the question itself cannot be answered with knowledge of the WordPress software/APIs, nor is any such knowledge necessary to answer, or beneficial in answering the question. This likely means that the question falls outside this site's scope - it would likely receive more attention and better answers over on Stack Overflow where there is a larger pool of SQL expertise.
    – bosco
    Sep 12, 2022 at 17:31

1 Answer 1

1
+50

You can do something like

  • Query the wp_posts table
  • Left join the source_name from wp_posmeta table
  • Left join the coverage_url from wp_posmeta table

Then select the data you want to pull from post, source_name and coverage_url result

Something like this should do

SELECT 
    post.ID, 
    post.post_title, 
    sn.meta_value as source_name, 
    cu.meta_value as coverage_url

FROM wp_posts as post

LEFT JOIN wp_postmeta as sn
    ON  post.ID = sn.post_id
    AND sn.meta_key = 'source_name'

LEFT JOIN wp_postmeta as cu
    ON  post.ID = cu.post_id
    AND cu.meta_key = 'coverage_url'

WHERE post.post_type = 'media_coverage'

You Query output should be somethng like

---- ID ----+---- post_title ----+---- source_name ----+---- coverage_url
-------------------------------------------------------------------------
   1        |     Title Here     |   Source Name Here  |  Coverage URL Here
1
  • Nice! Works great. The output is "ID | post_title | source_name | coverage_url". The most important appears to be using separate LEFT JOIN wp_postmeta... statements for each meta_value. Sep 14, 2022 at 15:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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