MySQL选择具有多个条件的查询

I have written a MySQL query but it doesn't seem to be working, because the result is empty. Is there any mistake in my code?

我写了一个MySQL查询，但它似乎没有工作，因为结果是空的。我的代码中有错误吗？

$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')"
);

4 个解决方案

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

这将选择满足所有四个条件的所有user_id。

#2

@fthiella 's solution is very elegant.

@fthiella的解决方案非常优雅。

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.

如果将来你想要显示的不仅仅是user_id，你可以使用连接，并且在一行中可以是你需要的所有数据。

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

如果要使用AND条件，并且条件在表中有多行，则可以使用JOINS示例：

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

其他的事情：建议使用预处理语句，因为mysql_ *函数不是SQL注入保存，并且将被弃用。如果您想尽可能少地更改代码，可以使用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.

使用此表中的索引。 user_id强烈建议使用和索引，并建议使用meta_key AND meta_value，以便更快地运行查询。

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.

如果使用AND，则“连接”一行的条件。因此，如果您希望AND条件为多行，首先必须从多行创建一行，如下所示。

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':

使用$ us_name ='Kovge'的查询结果$ us_yearselect ='2012'$ us_reg ='GaPa'，$ us_course ='PHP'：

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

So it should works.

所以它应该有效。

#3

also you can use "AND" instead of "OR" if you want both attributes to be applied.

如果要同时应用这两个属性，也可以使用“AND”而不是“OR”。

select * from tickets where (assigned_to='1') and (status='open') order by created_at desc;

#4

-2

You have conditions that are mutually exclusive - if meta_key is 'first_name', it can't also be 'yearofpassing'. Most likely you need your AND's to be OR's:

您有互相排斥的条件 - 如果meta_key是'first_name'，它也不能是'yearofpassing'。很可能你需要你的AND是OR的：

$result = mysql_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')")

#1