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