Can some one help me to display all the Posts and Postmeta of Specific custom post type
I have created a Custom Post Type 'Course' in a wordpress project, with some Custom Meta Boxes
I need to display all The Posts and Custom Meta Box Values of that Custom Post Type
I have tried the query below .
$query = "
SELECT $wpdb->posts.ID,$wpdb->posts.post_title,$wpdb->postmeta.meta_key
FROM $wpdb->posts
LEFT JOIN $wpdb->postmeta ON ( $wpdb->posts.ID = $wpdb->postmeta.post_id)
WHERE
$wpdb->postmeta.meta_key IN ('_course_code','_instructor_name')
AND
$wpdb->posts.post_type = 'course'
";
$results = $wpdb->get_results($query);
echo "<pre>";
print_r($results);
By Grouping this query results it only gives only one meta_key value
And it's giving me the result :
Please notice the id, and post_title being displayed twice with new index
Array
(
[0] => stdClass Object
(
[ID] => 10
[post_title] => introduction to wordpress
[meta_key] => _course_code
)
[1] => stdClass Object
(
[ID] => 10
[post_title] => introduction to wordpress
[meta_key] => _instructor_name
)
[2] => stdClass Object
(
[ID] => 13
[post_title] => introduction to hacking
[meta_key] => _course_code
)
[3] => stdClass Object
(
[ID] => 13
[post_title] => introduction to hacking
[meta_key] => _instructor_name
)
)
But i am expecting the results as
Array
(
[0] => stdClass Object
(
[ID] => 10
[post_title] => introduction to wordpress
[meta_key] => _course_code
[meta_key] => _instructor_name
)
[2] => stdClass Object
(
[ID] => 13
[post_title] => introduction to hacking
[meta_key] => _course_code
[meta_key] => _instructor_name
)
)
First of all, don't do $wpdb->posts, because hard to read. Secondly, missing GROUP BY sql query part.
$posts = $wpdb->posts;
$postmeta = $wpdb->postmeta;
$query = "SELECT $posts.ID, $posts.post_title, $postmeta.meta_key
FROM $posts
LEFT JOIN $postmeta ON ( $posts.ID = $postmeta.post_id)
WHERE
$postmeta.meta_key IN ('_course_code','_instructor_name')
AND $posts.post_type = 'course'
GROUP BY $posts.ID";
$results = $wpdb->get_results($query);
echo "<pre>";
print_r($results);
echo "</pre>";
I have manged to get the results by myself, putting my answer here hoping it will help others.
$query = "
SELECT $wpdb->posts.ID,$wpdb->posts.post_title,
PM1.meta_value as _course_code,
PM2.meta_value as _instructor_name
FROM $wpdb->posts
LEFT JOIN $wpdb->postmeta AS PM1 ON ( $wpdb->posts.ID = PM1.post_id AND PM1.meta_key = '_course_code')
LEFT JOIN $wpdb->postmeta AS PM2 ON ( $wpdb->posts.ID = PM2.post_id AND PM2.meta_key = '_instructor_name')
WHERE $wpdb->posts.post_type = 'course'
AND $wpdb->posts.post_status = 'publish'
AND ((PM1.meta_key = '_course_code') AND (PM2.meta_key='_instructor_name'))
GROUP BY $wpdb->posts.ID
ORDER BY $wpdb->posts.post_date DESC
";
$results = $wpdb->get_results($query);
echo "<pre>"; print_r($results); die("here");