wildcard's in a full-text search

[greg_brant]

hi, im looking to put wildcards in my full-text search... can it be done???

my query is

$query = "SELECT *,
MATCH(synopsis, title_english, title_original) AGAINST('$searchValue') AS score
FROM film_information WHERE MATCH(synopsis, title_english, title_original) AGAINST('$searchValue')
ORDER BY score DESC";

which works fine if your $searchValue is in there, but for example:

a search for 'plants' works fine, but a search for plant does not, so i would like to do a search for

'*plant*', which would pick up, "plants", or "eggplant".... etc etc

i hope you understand what im trying to do.

many thanks

Greg

[frezno]

you can use LIKE

 LIKE '%$searchstring%' 

[greg_brant]

you can use LIKE

 LIKE '%$searchstring%' 

will that work within the MATCH query. I need to do a full-text search. can i do a like accross 3 or more columns?

[frezno]

don't know offhand, why don't you try it

[AbstractHipHop]

i'm curious how that will play out also
post the results

[greg_brant]

i'm curious how that will play out also
post the results

"SELECT *,
			MATCH(synopsis, title_english, title_original) AGAINST('$searchValue') AS score
			FROM film_information WHERE synopsis LIKE '%$searchValue%'
			OR title_english LIKE '%$searchValue%'
			OR title_original LIKE '%$searchValue%'"

well thats the query i have, im not sure if its anydifferent to just doing 3 LIKE's with wild cards on the 3 columns

im no sql genius

[McGruff]

mysql manual (IN BOOLEAN MODE operators):
* An asterisk is the truncation operator. Unlike the other operators, it should be appended to the word, not prepended.

You can't search for prefixes - just suffixes. Great.

LIKE & RLIKE could be used instead:

"... WHERE col RLIKE '[[:]]buff[[:>:]]'" (buff not buffy or rebuff)

"... WHERE col LIKE '%buff%'" (buff, buffy, rebuffed)

"... WHERE col RLIKE '.*buff[[:>:]]'" (rebuff, buff, not buffy)

"... WHERE col RLIKE 'RLIKE '[[:]]buff.*'" (buff, buffy not rebuff)

Rather than adding OR clauses to search in multiple columns, you could use CONCAT. Not sure if one is significantly faster than the other.

I sometimes wonder just how useful MATCH is.

For example, fulltext searching is optimised for very large databases: if a search term occurs in more than 50% of rows it will be ignored (see http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html). That could be good or bad depending on your needs.

With the minimum word length set at >3, you can't search for "PHP" or "MVC".

Using LIKE/RLIKE and dynamic WHERE clause generation, it's possible to create a php search engine which supports all the usual boolean operators (+, -, *, ", etc) without using MATCH. I started building one a while back but haven't had time to finish it and compare performance.