0

I don't get any result in the SQL query below. I am trying to find user_id of users with custom usermeta fields. 

SELECT
    DISTINCT user_id
    FROM wp_usermeta
    WHERE (
        (
            (meta_key='user_school_subjects' AND meta_value LIKE '%history%')
            OR (meta_key='user_professional_courses' AND meta_value LIKE '%history%')
            OR (meta_key='user_language_tutoring' AND meta_value LIKE '%history%')
            OR (meta_key='user_music_tutoring' AND meta_value LIKE '%history%')
        )
        AND (meta_key='user_gender' AND meta_value='male')
    )

When I run the query in SQL PHPMYADMIN, I get an error -> Impossible WHERE Query

What's wrong with the query? Please help me fix the error in the above query.

UPDATE: Here is the full search query being built using a form.

global $wpdb;

    $user_ids = array();
    $user_query = null;

    if (isset($_REQUEST['search']) && !empty($_REQUEST['search']) ) {
        $s = $_REQUEST['search'];
        $user_query .= "((meta_key='first_name' AND meta_value LIKE '%".$s."%') OR (meta_key='last_name' AND meta_value LIKE '%".$s."%') OR (meta_key='display_name' AND meta_value LIKE '%".$s."%') OR (meta_key='user_nicename' AND meta_value LIKE '%".$s."%') OR (meta_key='user_login' AND meta_value LIKE '%".$s."%'))";
    }

    if (isset($_REQUEST['email']) && !empty($_REQUEST['email']) ) {
        $s = $_REQUEST['email'];
        if ($user_query) {
        $user_query .= " AND (meta_key='user_email' AND meta_value='".$s."')";
        } else {
        $user_query .= "(meta_key='user_email' AND meta_value='".$s."')";
        }
    }

    if (isset($_REQUEST['subject']) && $_REQUEST['subject'] !='' ) {
        $s = $_REQUEST['subject'];
        if ($user_query) {
        $user_query .= " AND ((meta_key='user_school_subjects' AND meta_value LIKE '%".$s."%')";
        $user_query .= " OR (meta_key='user_professional_courses' AND meta_value LIKE '%".$s."%')";
        $user_query .= " OR (meta_key='user_language_tutoring' AND meta_value LIKE '%".$s."%')";
        $user_query .= " OR (meta_key='user_music_tutoring' AND meta_value LIKE '%".$s."%'))";
        } else {
        $user_query .= "((meta_key='user_school_subjects' AND meta_value LIKE '%".$s."%')";
        $user_query .= " OR (meta_key='user_professional_courses' AND meta_value LIKE '%".$s."%')";
        $user_query .= " OR (meta_key='user_language_tutoring' AND meta_value LIKE '%".$s."%')";
        $user_query .= " OR (meta_key='user_music_tutoring' AND meta_value LIKE '%".$s."%'))";
        }
    }

    if (isset($_REQUEST['location']) && $_REQUEST['location'] !='' ) {
        $s = $_REQUEST['location'];
        if ($user_query) {
        $user_query .= " AND ((meta_key='user_address' AND meta_value LIKE '%".$s."%')";
        $user_query .= " OR (meta_key='user_zipcode' AND meta_value LIKE '%".$s."%'))";
        } else {
        $user_query .= "((meta_key='user_address' AND meta_value LIKE '%".$s."%')";
        $user_query .= " OR (meta_key='user_zipcode' AND meta_value LIKE '%".$s."%'))";
        }

    }

    if (isset($_REQUEST['gender']) && !empty($_REQUEST['gender']) ) {
        if ( $_REQUEST['gender'] != '') {
            $s = $_REQUEST['gender'];
            if ($user_query) {
            $user_query .= " AND (meta_key='user_gender' AND meta_value='".$s."')";
            } else {
            $user_query .= "(meta_key='user_gender' AND meta_value='".$s."')";
            }
        }
    }

    if (isset($_REQUEST['status']) && !empty($_REQUEST['status']) ) {
        if ( $_REQUEST['status'] == 'active') {
            $s = $_REQUEST['status'];
            if ($user_query) {
            $user_query .= " AND ( (meta_key='user_membership' AND meta_value='active')";
            $user_query .= " OR (meta_key='user_availability' AND meta_value='available'))";
            } else {
            $user_query .= "((meta_key='user_membership' AND meta_value='active')";
            $user_query .= " OR (meta_key='user_availability' AND meta_value='available'))";
            }
        }
        elseif ( $_REQUEST['status'] == 'inactive') {
            $s = $_REQUEST['status'];
            if ($user_query) {
            $user_query .= " AND ((meta_key='user_membership' AND meta_value='".$s."')";
            $user_query .= " OR (meta_key='user_availability' AND meta_value='unavailable'))";
            } else {
            $user_query .= "((meta_key='user_membership' AND meta_value='".$s."')";
            $user_query .= " OR (meta_key='user_availability' AND meta_value='unavailable'))";
            }
        }
    }

    if (isset($_REQUEST['nationality']) && $_REQUEST['nationality']!='any' ) {
        $s = $_REQUEST['nationality'];
        if ($user_query) {
        $user_query .= "AND (meta_key='user_nationality' AND meta_value='".$s."')";
        } else {
        $user_query .= "(meta_key='user_nationality' AND meta_value='".$s."')";
        }
    }


    if (!empty($user_query)) {
        $wp_users = $wpdb->get_results("SELECT DISTINCT user_id FROM $wpdb->usermeta WHERE ($user_query)"); }

EDIT: You can find the answer here: https://stackoverflow.com/questions/3735176/compex-mysql-left-join-using-multiple-entries-from-meta-tables

Improve this question
1
  • This is a MySQL question and not related to WordPress.
    – Wyck
    Jul 19, 2013 at 3:15

1 Answer 1

Reset to default
3

Look at your conditions:

WHERE (
  ( 
    (meta_key='user_school_subjects'
      AND meta_value LIKE '%history%')
    OR (meta_key='user_professional_courses'
        AND meta_value LIKE '%history%')
    OR (meta_key='user_language_tutoring'
        AND meta_value LIKE '%history%')
    OR (meta_key='user_music_tutoring'
        AND meta_value LIKE '%history%')
  )
  AND (meta_key='user_gender'
        AND meta_value='male'))

There is no way that you can have a meta key matching one of these--

  • user_school_subjects,
  • user_professional_courses,
  • user_language_tutoring,
  • user_music_tutoring

... that also matches this--

  • user_gender

If the key matches any of the first four, and it has to match one for that block to be true, then it automatically does not match the fifth. You can't ever get a true out of that. This clause is exactly what the server says-- impossible.

I would suggest WP_User_Query but I don't think it is capable of the logic you need.

You need to JOIN on the table, something like this:

SELECT DISTINCT wpmu.user_id
FROM wp_usermeta AS wpmu
LEFT JOIN wp_usermeta AS gender ON wpmu.user_id = gender.user_id
WHERE (
  (
    (wpmu.meta_key='user_school_subjects'
        AND wpmu.meta_value LIKE '%history%')
      OR (wpmu.meta_key='user_professional_courses'
          AND wpmu.meta_value LIKE '%history%')
      OR (wpmu.meta_key='user_language_tutoring'
          AND wpmu.meta_value LIKE '%history%')
      OR (wpmu.meta_key='user_music_tutoring'
          AND wpmu.meta_value LIKE '%history%')
    )
    AND (gender.meta_key='user_gender'
      AND gender.meta_value='male')
  )

I can't swear to that 100% since I can't test it without your data, but that is the idea.

Improve this answer
7
  • Thanks a tonne! It did give me the desired result. Now, I am wondering how to JOIN the table as I have multiple options save in the wp_usermeta table.
    – xtrawize
    Jul 19, 2013 at 2:52
  • My code does JOIN the table. JOIN the table on what? And why? To do what?
    – s_ha_dum
    Jul 19, 2013 at 13:39
  • To be very honest, I don't know a thing about SQL. So I am at loss.
    – xtrawize
    Jul 19, 2013 at 16:04
  • If you can't explain the problem, how do expect to get an answer?
    – s_ha_dum
    Jul 19, 2013 at 16:06
  • Sorry for not being descriptive. I have a list of custom fields saved in the wp_usermeta table such as user_address, user_availability and user_gender as you can see in the second code that I have pasted. I want to be able to pick a user based on a form with the inputs for all the fields. I hope I have been able to explain my objective. Thanks for your help.
    – xtrawize
    Jul 19, 2013 at 18:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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