SELECT multiple values from a single column in WordPress Database

3 Views

I am trying to output the post details, as well as some custom fields in wordpress’s database.
I have custom fields called ‘thumbnail’ and ‘video’ 

SELECT ID, post_title, post_content, wp_postmeta.meta_value
FROM wp_posts
INNER JOIN wp_postmeta ON wp_posts.id=wp_postmeta.post_id
WHERE wp_postmeta.meta_key = "thumnail"
OR wp_postmeta.meta_key = "video"

So far I am getting individual rows like this:

Read More
| ID  | Title     | Content     | Custom Fields                      |
|--------------------------------------------------------------------|
| 234 | INCEPTION | Content etc | wp-content/thumbnail/inception.jpg |
| 234 | INCEPTION | Content etc | wp-content/video/inception.flv     |

but what i would like is (ie one line per post) 

|ID |Title     |Content     |Thumbnail                  |Video                      |
|-----------------------------------------------------------------------------------|
|234|INCEPTION |Content etc |wp-content/..inception.jpg |wp-content/..inception.flv |

Could someone advise on how to separate the different values into columns within the SELECT statement?

Post Views: 3

Related posts

Leave a Reply

1 comment

  1. This process is called “pivot table” or “crosstab report”. There is no PIVOT command in MySQL but it can be done manually, here are two examples. The first example uses IF statement, the second one CASE statement. And an aggregate function is used to get rid of the duplicate rows.

    SELECT ID, post_title, post_content,
MAX( IF ( wp_postmeta.meta_key = "thumnail", wp_postmeta.meta_value, '' ) ) AS Thumbnail,
MAX( IF ( wp_postmeta.meta_key = "video", wp_postmeta.meta_value, '' ) ) AS Video
FROM wp_posts
INNER JOIN wp_postmeta ON wp_posts.id=wp_postmeta.post_id
WHERE wp_postmeta.meta_key IN ( "thumnail", "video" )
GROUP BY ID

SELECT ID, post_title, post_content,
MAX( CASE WHEN wp_postmeta.meta_key = "thumnail" THEN wp_postmeta.meta_value ELSE '' END ) AS Thumbnail,
MAX( CASE WHEN wp_postmeta.meta_key = "video" THEN wp_postmeta.meta_value ELSE '' END ) AS Video
FROM wp_posts
INNER JOIN wp_postmeta ON wp_posts.id=wp_postmeta.post_id
WHERE wp_postmeta.meta_key IN( "thumnail", "video" )
GROUP BY ID