FULLTEXT searching wont suffice, need workaround, any ideas?

Question

I can currently not use FULLTEXT indexes to search whole words because my server wont allow less characters than 4 in a search string.

I need a workaround then from you guys.

I want to be able to enter 'bmw 330' and then get results from mysql with whole matches like 'bmw 330'.

Should I consider a third party search engine for this?

How can I do this in PHP ?

Thanks

Answer 1

You could use something like Zend Lucene. It's written in PHP so you don't need to install anything.

Answer 2

So what if you write SQL like this:

SELECT * FROM table WHERE text_column LIKE '%bmw 330%'

?

Answer 3

You say "my server" so I suppose you have direct access to its configuration:

You can change the min characters required for an FULLTEXT search in MySQL, just change the following line (in my.cnf) to fit your needs:

[mysqld]
ft_min_word_len=3

Best wishes,
Fabian

Answer 4

SELECT * FROM table WHERE column LIKE '%bmw 330%' ?

Answer 5

If you want to match exactly, then you want

SELECT * FROM table WHERE text_column = 'bmw 330'

.

Answer 6

How about

SELECT * FROM table WHERE text_column LIKE '% bmw 330 %'

(note the spaces after and before the %)

?

Some limitations: It will find full words, but won't find

bmw 330(mint condition)
SPECIAL OFFER!!!!!bmw 330

Answer 7

All right, you have a number of suggestions here now. The best way is most certainly using a specialized search engine like Lucene.

If you can't use something like Lucene, I have an idea for a massively hacky workaround.

• Create a second text field in your table
• Whenever your text field is updated, write a copy of it into the second text field. Use a regex to find words less than 4 characters long and add a pre-defined string to every occurrence: bmw => bmwABCDEF
• For every incoming search query, use the same regex to add the string to every word < 4 characters. bmw 300 will then become bmwABCDEF 300ABCDEF and matched accordingly.

It's very hacky and needs to be throught through for possible side effects, but might work better than nothing.