WordPress中的SQL查询-获取不可能的Where错误

时间：2013-07-18 作者：xtrawize

我在下面的SQL查询中没有得到任何结果。我正在尝试查找具有自定义usermeta字段的用户的user\\u id。

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\')
    )
在SQL PHPMYADMIN中运行查询时，我得到一个错误->Impossible WHERE Query

查询有什么问题？请帮助我修复上述查询中的错误。

更新：这是使用表单构建的完整搜索查询。

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)"); }
编辑：您可以在此处找到答案：https://stackoverflow.com/questions/3735176/compex-mysql-left-join-using-multiple-entries-from-meta-tables

1 个回复
SO网友:s_ha_dum

看看你的情况：

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\'))
您不可能有一个元密钥与以下其中一个匹配--

  • user_school_subjects,
  • user_professional_courses,
  • user_language_tutoring,
  • user_music_tutoring
。。。这也与此相匹配--

  • user_gender
如果键匹配前四个键中的任何一个，并且必须匹配其中一个才能使该块为真，那么它将自动与第五个不匹配。你永远都找不到true 除此之外。这个子句正是服务器所说的--不可能。

我建议WP_User_Query 但我认为它不符合你需要的逻辑。

你需要JOIN 在桌子上，类似这样的东西：

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\')
  )
我不能百分之百地发誓，因为没有你的数据我无法测试它，但这就是我的想法。

结束
标签： mysql   sql   小码农  

相关推荐

用于从特定类别中选择帖子标题和帖子ID的SQL查询

我正在寻找SQL查询以选择帖子标题（&A）；发布特定类别的ID。我查看了wordpress表的结构，但它确实让我困惑。