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';
Improve this question
3
  • This is a question about SQL, not WordPress. But you need to use a JOIN statement. Commented Sep 12, 2022 at 14:36
  • This is a question about SQL and WordPress. So how do I use a JOIN statement? Commented 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. Commented Sep 12, 2022 at 17:31

1 Answer 1

Reset to default
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
Improve this answer
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. Commented Sep 14, 2022 at 15:36

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.