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.

Answer 2

@fthiella 's solution is very elegant.

If in future you want show more than user_id you could use joins, and there in one line could be all data you need.

If you want to use AND conditions, and the conditions are in multiple lines in your table, you can use JOINS example:

SELECT `w_name`.`user_id` 
     FROM `wp_usermeta` as `w_name`
     JOIN `wp_usermeta` as `w_year` ON `w_name`.`user_id`=`w_year`.`user_id` 
          AND `w_name`.`meta_key` = 'first_name' 
          AND `w_year`.`meta_key` = 'yearofpassing' 
     JOIN `wp_usermeta` as `w_city` ON `w_name`.`user_id`=`w_city`.user_id 
          AND `w_city`.`meta_key` = 'u_city'
     JOIN `wp_usermeta` as `w_course` ON `w_name`.`user_id`=`w_course`.`user_id` 
          AND `w_course`.`meta_key` = 'us_course'
     WHERE 
         `w_name`.`meta_value` = '$us_name' AND         
         `w_year`.meta_value   = '$us_yearselect' AND 
         `w_city`.`meta_value` = '$us_reg' AND 
         `w_course`.`meta_value` = '$us_course'

Other thing: Recommend to use prepared statements, because mysql_* functions is not SQL injection save, and will be deprecated. If you want to change your code the less as possible, you can use mysqli_ functions: http://php.net/manual/en/book.mysqli.php

Recommendation:

Use indexes in this table. user_id highly recommend to be and index, and recommend to be the meta_key AND meta_value too, for faster run of query.

The explain:

If you use AND you 'connect' the conditions for one line. So if you want AND condition for multiple lines, first you must create one line from multiple lines, like this.

Tests: Table Data:

          PRIMARY                 INDEX
      int       varchar(255)    varchar(255)
       /                \           |
  +---------+---------------+-----------+
  | user_id | meta_key      | meta_value|
  +---------+---------------+-----------+
  | 1       | first_name    | Kovge     |
  +---------+---------------+-----------+
  | 1       | yearofpassing | 2012      |
  +---------+---------------+-----------+
  | 1       | u_city        | GaPa      |
  +---------+---------------+-----------+
  | 1       | us_course     | PHP       |
  +---------+---------------+-----------+

The result of Query with $us_name='Kovge' $us_yearselect='2012' $us_reg='GaPa', $us_course='PHP':

 +---------+
 | user_id |
 +---------+
 | 1       |
 +---------+

So it should works.

Answer 3

You are querying a table that is designed as something I have seen referred to as a "Unified Content Model" (UCM). This table structure is chosen when maximum data flexibility in preferred. The disadvantage of this structure is that efficiency is compromised, the table typically suffers from being very bloated with data, and querying the table usually involved aggregate functions using a technique called a "pivot".

Here is how you can use a pivot to perform your query: (db-fiddle demo)

SELECT user_id 
FROM wp_usermeta
GROUP BY user_id
HAVING MAX(IF(meta_key = 'first_name', meta_value, NULL)) = 'mangesh'
   AND MAX(IF(meta_key = 'yearofpassing', meta_value, NULL)) = '2013'
   AND MAX(IF(meta_key = 'u_city', meta_value, NULL)) = 'n/a'
   AND MAX(IF(meta_key = 'us_course', meta_value, NULL)) = 'programming'

Effectively, the whole table gets grouped by the user_id column. In doing so, there is an interim one-to-many relationship formed. In other words, each respective user_id will have one-or-more-rows-worth of data (non-linear, not flat) which can be interrogated by MySQL's aggregate functions (e.g. MAX()).

The HAVING clause is where the filter logic must go after a GROUP BY (WHERE filtration occurs before GROUP BY). Within the "clusters of data", you can isolate data by checking its meta_key. When you find a row that matches the meta_key, you use its meta_value, if not you assign a differentiating default value. After all rows in the aggregate have been processed by the IF, you know that the row that you are looking for is the non-NULL value -- this is accessed by calling MAX(). Use this max value to compare against the actual value that you are filtering for. Repeat this filter as much as needed to satisfy your business logic.

I should state, also, that you are not using secure coding practices. mysql_query() is currently deprecated and should not exist in any currently live code for any reason. I recommend that you use mysqli's object oriented syntax and implement a prepared statement with bound parameters for security/stability.

$sql = "SELECT user_id 
        FROM wp_usermeta
        GROUP BY user_id
        HAVING MAX(IF(meta_key = 'first_name', meta_value, NULL)) = ?
           AND MAX(IF(meta_key = 'yearofpassing', meta_value, NULL)) = ?
           AND MAX(IF(meta_key = 'u_city', meta_value, NULL)) = ?
           AND MAX(IF(meta_key = 'us_course', meta_value, NULL)) = ?";

$stmt = $conn->prepare($sql);
$stmt->bind_param('ssss', $us_name, $us_yearselect, $us_reg, $us_course);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($user_id);
$stmt->fetch();
echo $user_id;