Adding additional condition to MySQL query causes empty response

3 Views

I’m having a problem with MySQL query. I should select members by their age using upper and lower limit to age. For example this query returns some results 

SELECT DISTINCT users.* 
FROM wp_users as users  
INNER JOIN wp_usermeta as mt 
ON (users.ID = mt.user_id) 
WHERE 1=1 
AND (
    (mt.meta_key LIKE 'user_birthday' AND mt.meta_value <> '' AND mt.meta_value <= 1985) 
    AND (mt.meta_key LIKE 'user_birthday' AND mt.meta_value <> '' AND mt.meta_value >= 1920)
) 
AND users.ID NOT IN (1,2,3) 
ORDER BY users.user_registered desc

This query retuns about 70 results. But when I add search for First name to this query like this:

Read More
SELECT DISTINCT users.* 
FROM wp_users as users 
INNER JOIN wp_usermeta as mt ON (users.ID = mt.user_id) 
WHERE 1=1 
AND (
    (mt.meta_key = '_upme_search_cache' AND mt.meta_value LIKE '%first_name::John%') 
    AND (mt.meta_key LIKE 'user_birthday' AND mt.meta_value <> '' AND mt.meta_value <= 1985) 
    AND (mt.meta_key LIKE 'user_birthday' AND mt.meta_value <> '' AND mt.meta_value >= 1920)
) 
AND users.ID NOT IN (1,2,3) 
ORDER BY users.user_registered desc

This returns 0 results, but I know I have 2 Johns in database. 

SELECT DISTINCT users.* 
FROM wp_users as users 
INNER JOIN wp_usermeta as mt ON (users.ID = mt.user_id) 
WHERE 1=1 AND (
    (mt.meta_key = '_upme_search_cache' AND mt.meta_value LIKE '%first_name::John%')
) AND users.ID NOT IN (1,2,3) 
ORDER BY users.user_registered desc

This one returns 2 records.

There are certainly no syntax errors here, but I guess that my logic is off somewhere?

I should mention that this is WordPress database with UPME plugin installed.

Did I make a mistake with joining tables or something else?

Post Views: 3

Related posts

Leave a Reply

2 comments

  2. This may be helpful..

    $args = array(                      
        'meta_query'   => array( 'meta_query' => array(
                        array(
                            'key' => '_upme_search_cache',
                            'value' => 'first_name::John',
                            'compare' => 'LIKE'
                        ),
                        array(
                            'key' => 'user_birthday',
                            'value' => '1985',
                            'compare' => '<='
                        ),
                        array(
                            'key' => 'user_birthday',
                            'value' => '1920',
                            'compare' => '>='
                        )
                    )),
        'include'      => array(),
        'exclude'      => array(1,2,3),
        'orderby'      => user_registered ,
        'order'        =>DESC                   
     );

 $users = get_users( $args );