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

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?

Solutions

@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.

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.

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;
Tags: PHP / Mysql / Wordpress

Similar questions

Having issues with wp_postmeta and wp_usermeta tables too big
We've got a WP+woocommerce site that is over 8 years old. The site has over 60,000 users and a similar number of orders. The wp_postmeta has over 4,000,000 records and wp_usermeta has over 1,500,000. This is causing all kinds of issues because the site was not updated regularly. The site wants to update the DB and it crashes every time, likely beca...
Matching multiple values in wp_usermeta to get email from wp_users table
I have created a simple html form to submit two values, license plate # and state, to a php script I have written. I am using wordpress. I want the script to look up a user in the wp_usermeta table, and select the user ID which has meta_value entries which match both license_plate and state submitted in the form. I then want the script to look up t...
MYSQL: I want to update all rows in a table with values from another table where values from the first table are equal to the second
I am trying to update a damaged WordPress terms relation (in tables wp_terms and wp_term_relationships). Some tags were entered in Wordpress with their "term_id" number instead of their "name", so instead of creating a relation with the original correct tag, a new tag was created with this number as its name. (For instance, if t...
Get user id from wp_usermeta table
I have stored the certain term ids in wp_usermeta table. i want to get the user id where the term id match with the user meta key 'location' value. i have tried it with below query but it is doing nothing.
Wordpress users and usermeta - joining multiple rows in one table to one row in another table
I want to create a view from both the wp_users and wp_usermeta tables so that I can query rows in the view from an external application. Basic auth details are stored in wp_users (e.g. username, password, email, id) and other fields are stored in key/value pairs in the wp_usermeta table. the wp_users table is structured like this: the wp_usermeta t...
Add multiple rows in Laravel DB Query (for migrating WordPress usermeta table into new Laravel table)
I'm migrating Users from WordPress to Laravel. I want to join the users and user_meta tables. Then I will import into a new table. In my user_meta table I have multiple rows assigned to the user_id. How do I import multiple rows with their own unique identifier. Eg. In the above example, I'd like to add first_name and last_name to the query output....

Also ask

We use cookies to deliver the best possible experience on our website. By continuing to use this site, accepting or closing this box, you consent to our use of cookies. To learn more, visit our privacy policy.