WordPress.org

Ready to get started?Download WordPress

Forums

How to list user by customer meta data using wp_user_query (4 posts)

  1. Gyrate360
    Member
    Posted 1 year ago #

    I have these two custom meta:
    first_child_birthday(format:10-15-2003) and second_child_birthday(format:10-15-2000)
    I just want get the users whose second_child is more than two years older than the first one!
    That means I want the sql like: the result of second_child_birthday minus first_child_birthday is greater than two years
    I did not see a way to this,anyone can help me ?
    Thanks in advance!
    Regard stevenlee

  2. vtxyzzy
    Member
    Posted 1 year ago #

    I don't know how you can do this using wp_user_query, but it can be done using $wpdb->get_results() like this:

    $sql = "
    SELECT u.*, um1.meta_value as first_birthday, um2.meta_value as second_birthday
    FROM $wpdb->users u
    JOIN $wpdb->usermeta um1 ON u.ID = um1.user_id
    JOIN $wpdb->usermeta um2 ON u.ID = um2.user_id
    WHERE um1.meta_key = 'first_child_birthday'
    AND um2.meta_key = 'second_child_birthday'
    AND (SUBSTR(um2.meta_value,7) - SUBSTR(um1.meta_value,7)) > 1
    ";
    $users = $wpdb->get_results($sql);
    // print_r($rows);
    foreach ($users as $user) {
       echo "ID:$user->ID  FIRST BDAY:$user->first_birthday SECOND BDAY:$user->second_birthday<br />";
    }
  3. Gyrate360
    Member
    Posted 1 year ago #

    Good,thanks,but I have another question: the format is variable,may be DD/MM/YY,so It will not work any more.how can I get there?

  4. vtxyzzy
    Member
    Posted 1 year ago #

    Actually, the code I gave you is not accurate. For example, first_child_birthday = '12-31-2000' and second_child_birthday = '01-01-2003' would be selected but the difference is actually only 2 years + 1 day.

    Here is a query that I think is correct:

    SELECT u.*, um1.meta_value as first_birthday, um2.meta_value as second_birthday
    ,CONCAT(SUBSTR(um1.meta_value,7),'-',SUBSTR(um1.meta_value,1,2),'-',SUBSTR(um1.meta_value,4,2)) AS first_date
    ,CONCAT(SUBSTR(um2.meta_value,7),'-',SUBSTR(um2.meta_value,1,2),'-',SUBSTR(um2.meta_value,4,2)) AS second_date
    ,DATEDIFF(CONCAT(SUBSTR(um2.meta_value,7),'-',SUBSTR(um2.meta_value,1,2),'-',SUBSTR(um2.meta_value,4,2))
    ,CONCAT(SUBSTR(um1.meta_value,7),'-',SUBSTR(um1.meta_value,1,2),'-',SUBSTR(um1.meta_value,4,2))) AS days_diff
    FROM $wpdb->users u
    JOIN $wpdb->usermeta um1 ON u.ID = um1.user_id
    JOIN $wpdb->usermeta um2 ON u.ID = um2.user_id
    WHERE um1.meta_key = 'first_child_birthday'
    AND um2.meta_key = 'second_child_birthday'
    AND DATEDIFF(CONCAT(SUBSTR(um2.meta_value,7),'-',SUBSTR(um2.meta_value,1,2),'-',SUBSTR(um2.meta_value,4,2))
    ,CONCAT(SUBSTR(um1.meta_value,7),'-',SUBSTR(um1.meta_value,1,2),'-',SUBSTR(um1.meta_value,4,2))) >= 1095

    Adding in code for different formats would make this much more complicated.

    The best thing you can do is keep all dates in 'YYYY-MM-DD' format. That would simplify the sql quite a bit.

    If you cannot do that, you must keep all dates in 'MM-DD-YYYY' format and use the code above.

Topic Closed

This topic has been closed to new replies.

About this Topic

Tags

No tags yet.