I have a script I need to adjust, but I'm not sure about the best way to approach it. The category field in the db has multiple entries, with spaces and/or commas between. I need it to return close or exact matches to what's entered in the form field "category".
Here's the query:
$Query = "SELECT * from $TableName WHERE category LIKE \"%$category%\"";
This works, but it's greedy. If I submit "art", it returns "art", "smart", "cart" and so on. If I remove the initial wildcard "%", it doesn't look past the first word in the string in the table.

What would be ideal would be if it saw "art", "artistic" or "artisan", but not the ones above that don't begin with the word/string.

Suggestions (mySQL newb here, so be gentle ;) )?

You would need a boolean search query then. Heres how you do it the boolean way..

$Query = "SELECT * FROM $TableName WHERE MATCH(category) AGAINST ('$category' IN BOOLEAN MODE)";

Also note that you can use MATCH(category) AGAINST ('$category' IN BOOLEAN MODE) only if you create a FULLTEXT index for 'category' column.

You can use the following SQL query to create the FULLTEXT INDEX.


CREATE FULLTEXT INDEX ft_category ON your_table _name (category);

Apart from the 3 character limit and various stop words, that seems to work pretty well, thanks. Appreciate the help.