1

I'm using custom post type to creating users in my wordpress website. I'm integrating an advance search for list down the users. In that case I need to search user by age ranges like 21 - 30 31 - 40, But I only have the date of birth of users in the DB. As you know in wordpress posts, it's stored as meta_key and meta_value in the wp_postmeta table.

I'm writing my own custom query to the search part: 

$queryStr = "SELECT DISTINCT ID, wposts.* FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta WHERE wposts.ID = wpostmeta.post_id";

//if nationality is not empty check it in the DB 
if($sltNationality != '' && $sltNationality != 'Any') :
    $queryStr .= " AND wpostmeta.meta_key = 'nationality' AND wpostmeta.meta_value = '$sltNationality'";
endif;

//if age range is not empty check it
if($age != '' && $age != 'Any') :
    $queryStr .= " check age between condition ";
endif;

//execute
$pageposts = $wpdb->get_results($queryStr, OBJECT);

Now I'm stucked in the age section. As search from the internet, glad I found a code from stackoverflow. get age from DOB

But have no idea how to use this inside my custom query. Please help me guys. thanks

Community
  • 1
  • 1
batMask
  • 734
  • 3
  • 17
  • 35

1 Answers1

1

You should let Wordpress build that meta query for you - you can use get_posts to achieve this.

Here is a suggestion :

$meta_query = array();
if($sltNationality != '' && $sltNationality != 'Any') { // nationality query
    array_push($meta_query, array(
        'key'       => 'nationality',
        'value'     => $sltNationality
    ));
}
if($age != '' && $age != 'Any') { // age query
    // assuming your select values are always in this format : 21 - 30, 31 -40 etc.
    $age = explode(' - ', $age); 
    $min_year   = (int)date('Y') - (int)$age[1];
    $max_year  = (int)date('Y') - (int)$age[0];
    // build a regex to get all the birth date in the year range
    $year_regex = array();
    for($y = $min_year; $y < $max_year; $y++) {
        array_push($year_regex, strval($y));
    }
    array_push($meta_query, array(
        'key'       => 'age',
        'value'     => '^[0-9]{2}-[0-9]{2}-('.implode('|', $year_regex).')$',
        'compare' => 'REGEXP'
    ));
}
$args = array(
    'posts_per_page' => -1,
    'post_type' => 'user',
    'meta_query' => $meta_query
);
$users = get_posts($args);

You should check that the post_type name is ok, and that the select values are always matching the "YY - YY" format - if not you will have to do some special conditions.

batMask
  • 734
  • 3
  • 17
  • 35
vard
  • 4,057
  • 2
  • 26
  • 46
  • I think your `foreach` should be an `for loop` right? ok let me try first appreciate. – batMask Jul 22 '15 at 09:15
  • I remove the substr(), Because its return only the last 2 digits of the year. Now its working. Awesome! thanks brother. – batMask Jul 22 '15 at 09:50
  • Oh ok I thought that it was stored in YY format not YYYY, that's why the substr was there. Glad that it helps :) – vard Jul 22 '15 at 09:52