Ask your WordPress questions! Pay money and get answers fast! (more info)

sql query to return post and meta values WordPress

  • SOLVED

Well, hello

Please can you help me put together an sql query to return the post title, post id and two custom field values, all in one data grid.

- not all posts have a custom field set
- the query will be run in mysql phpmyadmin

post_title id custom_field_1 custom_field_2
-------- ----- ------------- -------------
aaa 000 value 1 value 2
bbb 001 null value 4

Answers (1)

2013-03-23

Dbranes answers:

Hi, you can try this one:

SELECT wp_posts.post_title, wp_posts.ID, mt1.meta_value as field_1, mt2.meta_value as field_2
FROM wp_posts
LEFT JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id AND mt1.meta_key='somekey1')
LEFT JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id AND mt2.meta_key='somekey2')
WHERE wp_posts.post_type = 'post'
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private')
AND ((mt1.meta_key = 'somekey1') OR (mt2.meta_key = 'somekey2' ))
GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC


where you replace <strong>somekey1 </strong> and somekey2 with your own.


npeplow comments:

its working! one minor thing however, it only displays posts that have at least one custom field set - not those without


Dbranes comments:

ok, then you can try this one instead:

SELECT wp_posts.post_title, wp_posts.ID, mt1.meta_value as field_1, mt2.meta_value as field_2
FROM wp_posts
LEFT JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id AND mt1.meta_key='somekey1')
LEFT JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id AND mt2.meta_key='somekey2')
WHERE wp_posts.post_type = 'post'
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private')
GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC


Dbranes comments:

Was this query what you where looking for?