Some useful custom mysql query for get data form wordpress tables


Get post title, custom meta value and assigned category name using mysql query?

SELECT wp_posts.ID, wp_posts.post_title, wp_terms.name as category, myvideo.meta_value AS video_id, coutryval.meta_value AS country
FROM wp_posts
LEFT JOIN wp_postmeta as coutryval ON wp_posts.ID = coutryval.post_id
LEFT JOIN wp_postmeta as myvideo ON wp_posts.ID = myvideo.post_id
LEFT JOIN wp_term_relationships ON(wp_posts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_term_taxonomy ON(wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
LEFT JOIN wp_terms ON (wp_term_taxonomy.term_id = wp_terms.term_id)
WHERE 1
AND myvideo.meta_key = '_client_comp_vid_id'
AND coutryval.meta_key = '_country'
AND wp_posts.post_type = 'client_competitions' AND ((wp_posts.post_status = 'publish')) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC limit 0, 700

Get post title, custom meta value,assigned category name and featured image path using mysql query?

SELECT wp_posts.ID, wp_posts.post_title, wp_terms.name as category, myvideo.meta_value AS video_id, coutryval.meta_value AS country, img.meta_value AS image_id , imgtable.guid as image_path
FROM wp_posts
LEFT JOIN wp_postmeta as coutryval ON wp_posts.ID = coutryval.post_id
LEFT JOIN wp_postmeta as myvideo ON wp_posts.ID = myvideo.post_id
LEFT JOIN wp_postmeta as img ON wp_posts.ID = img.post_id
LEFT JOIN wp_posts as imgtable ON wp_posts.ID = imgtable.post_parent
LEFT JOIN wp_term_relationships ON(wp_posts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_term_taxonomy ON(wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
LEFT JOIN wp_terms ON (wp_term_taxonomy.term_id = wp_terms.term_id)
WHERE 1
AND myvideo.meta_key = '_client_comp_vid_id'
AND coutryval.meta_key = '_country'
AND img.meta_key = '_thumbnail_id'
AND wp_posts.post_type = 'client_competitions' AND ((wp_posts.post_status = 'publish')) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC 
Advertisements

Sr. Software Engineer

Posted in Wordpress

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: