Ask your WordPress questions! Pay money and get answers fast! Comodo Trusted Site Seal
Official PayPal Seal

Custom Post type, need to query by a meta field WordPress

  • SOLVED

I have a custom post type, memberlist

I then have multiple meta fields, one being the member's name. member_name

I need to query that custom post type and be able to show the post by the first leter of the member_name, ie; using 'a' in the query would return all members who's name starts with an 'a', using 'b' in the query would return all members who's name starts with a 'b', etc...

Can someone help me with this query?

Answers (3)

2010-10-21

Michael Fields answers:

This worked for me:

global $wpdb;
$posts = $wpdb->get_results( "
SELECT *
FROM $wpdb->posts AS p, $wpdb->postmeta as pm
WHERE p.post_type = 'memberlist'
AND p.ID = pm.post_id
AND pm.meta_key = 'member_name'
AND LEFT( pm.meta_value, 1 ) = 'a'
" );


69developer comments:

Then how would I use the above code to display the post(s) returned? Can I still use the Wordpress Loop?


Michael Fields comments:

Something like this should do it:
<code>
global $wpdb;
$members = $wpdb->get_results( "
SELECT *
FROM $wpdb->posts AS p, $wpdb->postmeta as pm
WHERE p.post_type = 'memberlist'
AND p.ID = pm.post_id
AND pm.meta_key = 'member_name'
AND LEFT( pm.meta_value, 1 ) = 'a'
" );

$post_backup = $post;
foreach( (array) $members as $post ) {
setup_postdata( $post );
the_title();
the_content();
}
$post = $post_backup;
<code>


69developer comments:

One last question on this, I promise. In your foreach, how would you get the value of the meta fields?

I'm use to something like this
`$member_email = $custom["member_email"][0];`


Michael Fields comments:

I always do something like this:

$member_email = get_post_meta( $post->ID, 'member_email', true );
if( !empty( $member_email ) ) {
print $member_email;
}


69developer comments:

Thanks, works great! As soon as the last poster on here replys to a question, I will close out and award to you and him. Since you answered first, you will get 80%.

Thanks, and I just post another similar question.

2010-10-21

Rares Cosma answers:

This function returns an array of post ids matching the specified meta prefix:


function get_cpt_by_meta_prefix($args) {
// Database abstraction
global $wpdb;

// Default arguments
$defaults = array(
'post_type' => 'post',
'meta_key' => false,
'meta_prefix' => false
);

// Extract arguments for easier access
extract(wp_parse_args($args, $defaults));

if($meta_key and $meta_prefix){
$ids = array();

// Note: you can modify this query to get more post data
$query = "SELECT $wpdb->posts.ID
FROM $wpdb->posts
LEFT JOIN $wpdb->postmeta ON ($wpdb->posts.ID = $wpdb->postmeta.post_id)
WHERE $wpdb->postmeta.meta_key = '$meta_key'
AND $wpdb->postmeta.meta_value like '$meta_prefix%'
AND $wpdb->posts.post_status = 'publish'
AND $wpdb->posts.post_type = '$post_type'
ORDER BY $wpdb->postmeta.meta_value ASC";

$results = $wpdb->get_results($query, ARRAY_A);

// Parse the results
foreach($results as $result){
$ids[] = $result['ID'];
}

return $ids;
}

return false;
}



An example call:

$args = array(
'post_type' => 'memberlist',
'meta_key' => 'member_name',
'meta_prefix' => 'a'
);

$post_ids = get_cpt_by_meta_prefix($args);


69developer comments:

Then once I have a list of the post id's, I can you the query_posts function? If so what would be the actual query_post() arguments to display all the id's that were found?

2010-10-21

Rok Don answers:

SELECT distinct wposts.*
FROM $wpdb->posts wposts
WHERE
wposts.post_status = 'publish'
AND wposts.post_type = 'post'
AND wposts.ID IN(
SELECT post_id FROM $wpdb->postmeta wpostmeta WHERE
(wpostmeta.meta_key = 'member_name' AND wpostmeta.meta_value LIKE 'a%')
)
ORDER BY wposts.post_date DESC