CODEWITHSUNDEEP
sqlsearchtagsmatchagainst
Van Zark
Van Zark

Reputation: 25

SQL: MATCH AGAINST all elements in an array

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.

Upvotes: 1

Views: 733

Answers (1)

Nick
Nick

Reputation: 147166

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.

Upvotes: 1

Related Questions