WordPress SQL query to fetch the meta_key names in a custom post type

1 Views

Using WP dataTables plugin and my MySQL sucks so please bear with me.

I’m trying to fetch multiple associated meta_keys (custom field names) that are allocated to a custom post type in WordPress, and display the fields as the table header with the custom field values in the rows underneath the header.

Read More

My SQL below kinda works. While it pulls in a colum called ‘meta_key’ and ‘meta_value’, it has the actual names of the key as it’s data. I want the meta_key value itself as the list returned. 

SELECT * FROM wp_posts INNER JOIN wp_postmeta 
ON wp_posts.ID = wp_postmeta.post_id 
WHERE 1=1 AND wp_posts.post_type = 'my_custom_post' 
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') 
AND (wp_postmeta.meta_key = 'call_date' OR wp_postmeta.meta_key = 'call_time' OR wp_postmeta.meta_key = 'caller_number' OR wp_postmeta.meta_key = 'call_duration' OR wp_postmeta.meta_key = 'call_status' ) 
ORDER BY wp_posts.menu_order, wp_posts.post_date DESC LIMIT

So this creates a table with the header as ‘meta_key’ but I want the custom field name itself as the header. Hope this makes sense.

PS I’ve also tried 

SELECT caller_number, call_duration, call_status, call_date, call_time
FROM wp_posts
WHERE post_type =  'my_custom_post'
AND post_status =  'publish'
FROM wp_postmeta
WHERE post_id = post_id

But it shows no data…

Seriously, any help here would be awesome.

Post Views: 1

Related posts

Leave a Reply

2 comments

  1. for specific keys

    get_post_meta($postid, '_metakey', single);

    for all keys 

    get_post_meta($postid);

    so you can do a foreach loop if you return just the custom posts in your sql query. But for posts, wp_query does a good job of returning posts rather than custom mysql queried.

  2. See what is all returning…

    global $wpdb;
global $post;

$cpt = "my_custom_post";

$sql = "SELECT * FROM {$wpdb->prefix}posts INNER JOIN {$wpdb->prefix}postmeta 
ON {$wpdb->prefix}posts.ID = {$wpdb->prefix}postmeta.post_id 
WHERE 1=1 AND {$wpdb->prefix}posts.post_type = '$cpt' 
AND ({$wpdb->prefix}posts.post_status = 'publish' OR {$wpdb->prefix}posts.post_status = 'private') 
AND ({$wpdb->prefix}postmeta.meta_key = 'call_date' OR {$wpdb->prefix}postmeta.meta_key = 'call_time' OR {$wpdb->prefix}postmeta.meta_key = 'caller_number' OR {$wpdb->prefix}postmeta.meta_key = 'call_duration' OR {$wpdb->prefix}postmeta.meta_key = 'call_status' ) 
ORDER BY {$wpdb->prefix}posts.menu_order, {$wpdb->prefix}posts.post_date DESC";

$data = $wpdb->get_results($sql);

echo "<pre>";
var_dump($data);
echo "</pre>";

die();