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

3 comments

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.

   Log in to Reply

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.

   Log in to Reply

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;
   

   Log in to Reply