选择在 wp_usermeta 表中的多行上具有合格数据的用户

2022-01-22 00:00:00 pivot group-by php mysql wordpress

我正在尝试查找具有所有四个限定值的 user_id - 每个值都位于数据库表的不同行中.

I am trying to find the user_id which has all four qualifying values -- each in a different row of the database table.

我要查询的表是 wp_usermeta:

The table that I am querying is wp_usermeta:

Field       Type                   Null    Key    Default    Extra
---------------------------------------------------------------------------
umeta_id    bigint(20) unsigned            PRI               auto_increment
user_id     bigint(20) unsigned            IND    0  
meta_key    varchar(255)           Yes     IND    NULL   
meta_value  longtext               Yes            NULL   

我写了一个 MySQL 查询,但它似乎没有工作,因为结果是空的.

I have written a MySQL query but it doesn't seem to be working, because the result is empty.

$result = mysql_query(
     "SELECT user_id 
      FROM wp_usermeta 
      WHERE 
         (meta_key = 'first_name' AND meta_value = '$us_name') AND         
         (meta_key = 'yearofpassing' AND meta_value = '$us_yearselect') AND 
         (meta_key = 'u_city' AND meta_value = '$us_reg') AND 
         (meta_key = 'us_course' AND meta_value = '$us_course')"
);

如何返回与所有这四行相关的 user_id?

How do I return the user_id that relates to all four of these rows?

推荐答案

我会使用这个查询:

SELECT
  user_id
FROM
  wp_usermeta 
WHERE 
  (meta_key = 'first_name' AND meta_value = '$us_name') OR 
  (meta_key = 'yearofpassing' AND meta_value = '$us_yearselect') OR 
  (meta_key = 'u_city' AND meta_value = '$us_reg') OR
  (meta_key = 'us_course' AND meta_value = '$us_course')
GROUP BY
  user_id
HAVING
  COUNT(DISTINCT meta_key)=4

这将选择满足所有四个条件的所有 user_id.

this will select all user_id that meets all four conditions.

相关文章