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