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

group-by, mysql, php, pivot, wordpress

Solution

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.

Problem

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?

Original source