How do I group and map identical column values to new column with corresponding values for posts

Question

I currently am using the following code to get a list of posts, showing post_name, post_content and post_id, where there is a custom field (meta_key) used named MediaLink.

 SELECT wp_posts.post_name, wp_posts.post_content, wp_posts.ID 
 FROM wp_posts, wp_postmeta
 WHERE wp_posts.ID = wp_postmeta.post_id
 AND wp_postmeta.meta_key = "MediaLink"

Here is a visual example of my current table from that query:

post_name | post_content | post_id |

---

Name 1         Content 1        1
Name 2         Content 2        2

I would like to have the following table:

post_name | post_content | post_id | Artist | Producer

---

Name 1         Content 1        1      Madonna    Philip    
Name 2         Content 2        2      Prince     Jack

The problem is that Artist & Producer is currently in the wp_postmeta table as :

meta_key | meta_value | post_id | meta_id |
___________________________________________
Artist     Madonna         1         1
Artist     Prince          2         2
Producer   Philip          1         3
Producer   Jack            2         4

What do I add to my initial query to to first query to accomplish this?

I want to show the producer and Artist column for post rows that both do and don't have the Artist and Producer fields filled out yet.

This way I can export this data to CSV with PhpMyAdmin and update thousands of posts, affecting all the fields I need per post in one go.

Answer 1

Something like this should do the trick (I can't test properly without your data, so let me know if you get any weird results):

 SELECT p.post_name,
        p.post_content,
        p.ID,
        art.meta_value as artist,
        prd.meta_value as producer
 FROM wp_posts p
      JOIN wp_postmeta pm on p.ID = pm.post_id
      LEFT JOIN wp_postmeta art on p.ID = art.post_id and art.meta_key = 'Artist' 
      LEFT JOIN wp_postmeta prd on p.ID = prd.post_id and prd.meta_key = 'Producer' 
WHERE pm.meta_key = "MediaLink"

By way of explanation, you can use a table several times in a query if you give it an alias. I've used pm, art, and prd to reference wp_postmeta under different conditions.

I changed your syntax to ANSI join syntax, as I find it easier to see what's going on: the join conditions are separate from the criteria (though there's a grey area with the Artist and Producer conditions - see below).

Since you said

I want to show the producer and Artist column for post rows that both do and don't have the Artist and Producer fields filled out yet.

I made the joins LEFT JOINs, which will always return the row from the left table, add NULL from the right table if the row doesn't exist. I put the Artist / Producer in the join condition, not the WHERE clause because it's evaluated after the join and you'd lose those NULL records.