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)
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?