0

I have an ACF field 'date_of_birth' which stores the user's birthday, examples: 20201226, 20151225, 19980701

Goal: get all users which have their birthday this month, or next month.

I currently have this:

    $users_start_date_boundary = gmdate( 'Ymd', strtotime( '-1 month' ) );
    $users_end_date_boundary   = gmdate( 'Ymd', strtotime( '+2 month' ) );

    $wp_user_query = new WP_User_Query(
        [
            'fields'     => 'all_with_meta',
            'orderby'    => 'date_of_birth',
            'order'      => 'ASC',
            'meta_query' => [
                [
                    'key'     => 'date_of_birth',
                    'value'   => [ $users_start_date_boundary, $users_end_date_boundary ],
                    'compare' => 'BETWEEN',
                    'type'    => 'DATE',
                ],
            ],
        ]
    );

    return $wp_user_query->get_results();

The problem with this however, that it only gets users with a birthyear of 2020.

How do I get all users, based on the given month boundaries, while ignoring the year/day the person is born in?

11
  • WP / the db could have a problem with the format the dates is in. Can you instead save it as a timestamp? Or well formatted (i.e. 2020-12-26)?
    – kero
    Nov 10, 2020 at 14:20
  • 1
    @kero ACF doesn't allow database configurations, I'll have to deal with the current format. I can't save it as a timestamp, but I can use a date time picker instead of a datepicker if that would help Nov 10, 2020 at 14:40
  • You definitely need a more complex code to do what you want, and I have an idea what is needed to be done. Unfortunately (or fortunately :) ) I don't use ACF with any of my sites so to test it I need to know how exactly this meta field appeared in the database. Can you attach an example to you question? Nov 10, 2020 at 15:07
  • 1
    I think you're going to have to write your own SQL query to do this, rather than using WP_User_Query. You probably want the MONTH() function to extract the month field from the date. However I don't think the meta value will be stored as a MySQL date, so maybe you might have to use a substring match? Either way I'd start by getting the query working in SQL.
    – Rup
    Nov 10, 2020 at 17:31
  • 1
    @IvanShatsky Alternatively, you can just store the month in an additional meta field using the acf/update_value hook and then use the >= and <= meta query on that field. Btw the meta values should look like this
    – kero
    Nov 10, 2020 at 18:22

2 Answers 2

1

Here is the code based on custom SQL query as suggested by @Rup that should work for you:

// get '-1 month' and '+2 month' dates as an array('YYYY', 'MMDD')
$before = str_split( gmdate( 'Ymd', strtotime( '-1 month' ) ), 4 );
$after = str_split( gmdate( 'Ymd', strtotime( '+2 month' ) ), 4 );
// if the before/after years are the same, should search for date >= before AND <= after
// if the before/after years are different, should search for date >= before OR <= after
$cmp = ( $before[0] == $after[0] ) ? 'AND' : 'OR';
// SQL query
$users = $wpdb->get_col( $wpdb->prepare(
    "SELECT user_id FROM {$wpdb->usermeta} WHERE meta_key = %s AND (SUBSTRING(meta_value, 5, 4) >= '%s' %s SUBSTRING(meta_value, 5, 4) <= '%s')",
    'date_of_birth', $before[1], $cmp, $after[1]
));

Update

Maybe I misunderstand your question looking at your code. The above code would give the list of users whose birthday passed no more than month ago or will happen no more than two months after the current date. To get the list of all users which have their birthday this month or next month, use the following code:

$current = gmdate( 'm' );
$next = sprintf( "%02d", $current % 12 + 1 );
$users = $wpdb->get_col( $wpdb->prepare(
    "SELECT user_id FROM {$wpdb->usermeta} WHERE meta_key = %s AND (SUBSTRING(meta_value, 5, 2) = '%s' OR SUBSTRING(meta_value, 5, 2) = '%s')",
    'date_of_birth', $current, $next
));
0

I believe since ACF stores the values as Ymd you have to write some kind of workaround. One possible solution could be that you hook into saving the field and additionally store the month in another meta field. This would allow to query for => and <= with the meta query.

Another solution is to not store the date in an arbitrary format but one that the database understands and then using the database's existing functions.

\add_filter('acf/update_value/type=date_picker', function($value) {
    if (empty($value)) {
        return $value;
    }

    $dt = \DateTime::createFromFormat('Ymd', $value);
    return $dt->format(\DateTime::ATOM);
});

\add_filter('acf/load_value/type=date_picker', function($value) {
    if (empty($value)) {
        return $value;
    }

    $dt = new \DateTime($value);
    return $dt->format('Ymd');
});

These two filters will change any date picker to store the date in a well formed date string (like 2005-08-15T15:52:01+00:00) which can easily be understood by the database. You can also just target this specific field, read more about it (doc for acf/update_value and doc for acf/load_value). (Attention! If you already have dates saved in the db, you should manually transform them to new format before applying this code change!)

To retrieve the user IDs you can use $wpdb with a custom query like so

$month = date('m');
$userIds = $wpdb->get_col($wpdb->prepare(
    "SELECT user_id FROM {$wpdb->usermeta} WHERE meta_key = %s AND (MONTH(meta_value) = %d OR MONTH(meta_value) = %d)",
    'date_of_birth',
    $month,
    ($month % 12) + 1
));

(($month % 12) + 1 will get the next month, see this explanation)

It seems that WP_User_Query is not able to retrieve a list of users by their ID, so you would need to call get_userdata() for each user ID, which is probably not optimal - but that depends on your use case.

3
  • The issue with this solution is that it ignores the year the person was born in. If someone was born in 1998, on December 12th it woudn't match in your example. Nov 10, 2020 at 15:01
  • @fanatic21221 Ah .. you're right - I was too quick, sorry. Let me re-think this issue. Would it be a solution to change how ACF stores/retrieves date or do you have this already running and would need to update existing values?
    – kero
    Nov 10, 2020 at 16:34
  • @fanatic21221 Please see the updated solution.
    – kero
    Nov 10, 2020 at 18:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.