从表A中获取随机结果（如果它存在于表B中）并满足查询中的WHERE语句

问题描述：

I have two tables; one table with users, a second table contains the categories.

On the site's frontend are a bunch of filters to filter users. One of the filters is a dropdown with checkboxes that have category values. E.g: admin, user, publisher etc can be selected.

When multiple checkboxes have been checked get a random user from users but only random user that has all selected categories.

The users have an unique id. The categories have an unique id, the category value (admin, publisher, user etc) and the user-id so it can be tied together I think.

Important note:

($value = the posted value from checkbox, also keep in mind that the categories.category = $value part is added dynamically depending on the amount of selections, I append that using a foreach)

My current query:

$sql = "SELECT users.*, categories.*
       FROM users
       INNER JOIN categories
       ON users.id = categories.user_id
       WHERE categories.category = '$value' AND 
             categories.category = '$value' 
             --> continues endlessly depending on the amount of selections that have been made.
       ORDER BY RAND()
       LIMIT 0,1";

For some reason this only works when one checkbox is selected. When a second one, or more a selected, it breaks, no errors, it just simply doesn't find any result.

I hope my question is clear, if you need any future information, please let me know!

我有两张桌子; 一个表与用户，第二个表包含类别。 p>

在网站的前端有一堆过滤器来过滤用户。其中一个过滤器是带有类别值的复选框的下拉列表。例如：可以选择管理员，用户，发布商等。 p>

当选中多个复选框时，从用户获取随机用户，但只有具有所有选定类别的随机用户。 p> \ n

用户拥有唯一的ID。这些类别具有唯一的ID，类别值（管理员，发布者，用户等）和用户ID，因此我认为它可以捆绑在一起。 p>

重要说明： p>

（$ value code> =复选框中发布的值，同时请记住， categories.category = $ value code>部分是动态添加的，具体取决于选择的数量，我追加使用foreach） p>

我当前的查询： p>


  $ sql =“SELECT users。*，categories  。* 
 FROM users 
 INNER JOIN类别
 ON users.id = categories.user_id 
 WHERE categories.category ='$ value'AND 
 categories.category ='$ value'
  - ＆gt;继续 无休止地取决于已经做出的选择量。
 ORDER BY RAND（）
 LIMIT 0,1“; 
  code>  pre> 
 
 出于某种原因，这只能起作用 选中一个复选框时 当第二个或更多被选中时，它会中断，没有错误，它只是根本找不到任何结果。 p> 
 
 
我希望我的问题很清楚，如果你需要任何未来的信息 ，请告诉我！ p> 
  div>

答

Assuming that $values is an array of the category IDs (I'll assume integers), you could run a query like:

SELECT
  users.*
FROM
  users
INNER JOIN
  categories ON users.id = categories.user_id
WHERE
  categories.category IN (" . implode(',', $values) . ")
GROUP BY
  users.id
HAVING
  COUNT(DISTINCT categories.category) >= " . count($values) . "
ORDER BY
  RAND()
LIMIT 1

I've made an example fiddle.

Edit

If the categories are strings then you'd need to ensure proper quotation, i.e. change the WHERE to:

categories.category IN ('" . implode("','", $values) . "')

Or if using prepared statements (which is ideal) you'd bind implode(',', $values) using:

FIND_IN_SET(categories.category, ?) != 0

从表A中获取随机结果（如果它存在于表B中）并满足查询中的WHERE语句

相关推荐