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

Question

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!

Answer 1

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