I have an array of "tags" stored in a cookie. They are formatted as a series of words separated by ,'s. I'm trying to return all rows in a table where every "tag" of that array is found in the "search_tags" column.
I currently have:
$stmt = $conn->prepare("SELECT * FROM users WHERE MATCH search_tags AGAINST('". $_COOKIE['tags'] ."' IN BOOLEAN MODE);");
This almost works, but returns all the rows where ANY of the tags match. Instead of requiring ALL of the $_COOKIE['tags'] to match the "search_tags" column.
An alternative solution could be where it stays as an OR query but instead sorts the results by number of matches or relevance.
You can use the + operator in boolean mode to assert that a word must be present, so if you convert the tags from a comma separated list to a + separated list that should give you the result you want:
$tags = '+' . implode(' +', explode(',', $_COOKIE['tags']));
$stmt = $conn->prepare("SELECT * FROM users WHERE MATCH search_tags AGAINST('$tags' IN BOOLEAN MODE);");
Note if you might have spaces after the commas, you should use preg_split('/,\s*/', $_COOKIE['tags']) instead of the explode.