CODEWITHSUNDEEP
phpmysqlwordpress
vjks
vjks

Reputation: 383

While joining it to wp_posts, how can I put all the values of wp_postmeta table in one row?

I want to join wp_posts and wp_postmeta table like this: 

SELECT wp_posts.id, wp_posts.post_title, wp_posts.post_author, wp_postmeta.meta_key, wp_postmeta.meta_value
FROM wp_posts 
LEFT JOIN wp_postmeta ON wp_posts.id = wp_postmeta.post_id
WHERE post_type = 'job';

But I need the values of the key-value pairs in the wp_postmeta table to be in one row; so that the select statement looks something like this: 

SELECT wp_posts.id, wp_posts.post_title, wp_posts.post_author, wp_postmeta.meta_key1, wp_postmeta.meta_key2, wp_postmeta.meta_key3, wp_postmeta.meta_key4

This is how 3 rows appear inside the wp_postmeta table:

**post_id meta_key meta_value** 23841 job_start 1488386107 23841 job_requirements null 23841 job_employment Full-Time

How can I modify by select statement to put the meta_values in one row of the result of the select statement for each post_id? Any help is appreciated.

Upvotes: 2

Views: 1051

Answers (2)

vjks
vjks

Reputation: 383

This is the solution that I was looking for. based on post_id, this allows me to put the meta_values of wp_postmeta in a single row with the column values of wp_posts: 

SELECT  wp_posts.ID, wp_posts.post_title,  wp_posts.post_author, pm1.meta_value as job_start_date, pm2.meta_value as job_end_date 
FROM wp_posts 
LEFT JOIN wp_postmeta AS pm1 ON (wp_posts.ID = pm1.post_id AND pm1.meta_key='job_start')
LEFT JOIN wp_postmeta AS pm2 ON (wp_posts.ID = pm2.post_id  AND pm2.meta_key='job_end') 
WHERE wp_posts.post_type = 'job';

Here's where I found this solution: http://subharanjan.com/sql-query-to-get-post-and-its-meta_values-as-one-row-of-record-single-data-grid/

I'm sorry if I hadn't framed my question clearly.

Upvotes: 2

Nikko Madrelijos
Nikko Madrelijos

Reputation: 545

If you add asterisk() on the fields of wp_postmeta e.g. wp_postmeta. That means you are getting all its columns.

SELECT wp_posts.id, wp_posts.post_title, wp_posts.post_author, wp_postmeta.*
FROM wp_posts 
LEFT JOIN wp_postmeta ON wp_posts.id = wp_postmeta.post_id
WHERE post_type = 'job';

Try it.

Upvotes: 0

Related Questions