Select user having qualifying data on multiple rows in the wp_usermeta table

Question

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

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    

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')" ); 

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

Answer 1

I would use this query:

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 

this will select all user_id that meets all four conditions.