Select user having qualifying data on multiple rows in the wp_usermeta table
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.
@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.