CODEWITHSUNDEEP
phpmysqlfull-text-search
Tom
Tom

Reputation: 131

Fulltext search with PHP and mySQL - altering table not an option

I have a php/mysql query question regarding a half-finished project I took over recently.

The project is an online shop programmed in php using a mysql database which is also connected by an ERP (C++) software. The ERP and the half-finished online shop were done by the same programmer, but my customer is quite unsatisfied by the result of the shop. Since the programmer refuses to work together with me, I'm stuck regarding an otherwise quite simple problem.

I need to do a fulltext search across two fields which both do have a fulltext index. It'd be a simple "SELECT ... MATCH ... AGAINST", but it does not work as expected. If I take e.g "tree" as a search item, the query finds everything with "tree" but not "Tree", or "TREE" and so on... My first guess was to change the collations of the fields into something different than latin1_general_cs but unfortunately the ERP doesn't seem to like that very much.

I read somewhere that there's some possibility to convert the needed fields into something more useful on-the-fly within the query string, but I couldn't figure it out.

Maybe someone had a similar problem?

Upvotes: 0

Views: 159

Answers (2)

monitorjbl
monitorjbl

Reputation: 4350

I believe you can stick the columns in your MATCH clause in a LOWER() function. If you make sure to only pass lower case strings into your AGAINST clause (or wrap them in their own LOWER()), you won't have this problem.

Upvotes: 0

Sashi Kant
Sashi Kant

Reputation: 13465

Try this ::

Select * from myTable where LOWER(column) like '%tree%'

you will get all Tree, TREE, tree from the table if present

Upvotes: 1

Related Questions