CODEWITHSUNDEEP
mysqlpreg-replace
Jaya Prakash
Jaya Prakash

Reputation: 93

How to use preg_replace() function inside mysql match() against ()

I just compiled preg_replace() for mysql when i trying to use preg_replace() before full text search match () against () it return mysql syntax error. My code looks similar to

select *, match(name, preg_replace('/[^a-z0-9A-Z]+/i', ' ', `column`)) against ('$name') as score
from files
order by score desc

... and triggers this error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('/[^a-z0-9A-Z]+/i', ' ', column)) against ('$name') as score from files ' at line 1

Upvotes: 0

Views: 5358

Answers (1)

Álvaro González
Álvaro González

Reputation: 146588

If we have a look at the manual page for MATCH...AGAINST we can see that MATCH() expects a list of columns:

MATCH (col1,col2,...) AGAINST (expr [search_modifier])
[...]
MATCH() takes a comma-separated list that names the columns to be searched

However, you provide the result of a function call:

match(name, preg_replace('/[^a-z0-9A-Z]+/i', ' ', `column`))

The logic behind this is that full-text search is designed to use special indexes created on table columns to find stuff. You cannot perform full text searches in random strings.

There's a further explanation in the Full-Text Restrictions chapter:

The MATCH() column list must match exactly the column list in some FULLTEXT index definition for the table, unless this MATCH() is IN BOOLEAN MODE on a MyISAM table. For MyISAM tables, boolean-mode searches can be done on nonindexed columns, although they are likely to be slow.

Edit: I don't know what you have in mind but the whole point of full-text search is to perform smart fuzzy matching on natural language texts. If you tranform "John's fiancée" into "Johns fiance" before searching there's nothing that full-text can do for you. Are you sure you don't want good old exact matching?

WHERE preg_replace('/[^a-z0-9A-Z]+/i', ' ', `column`)='$name'

... or subpattern matching?

WHERE preg_replace('/[^a-z0-9A-Z]+/i', ' ', `column`) LIKE '%$name%'

Whatever, the best way to perform full-text on arbitrary strings is to get them stored into the table. It'll even improve performance for two reasons:

  • You don't need to recalculate the values on every search
  • You can actually use a full-text index

You can keep the value in sync manually (on every insert or update) or write a trigger to do it.

Upvotes: 2

Related Questions