MySQL fulltext too restrictive - Databases forum at WebmasterWorld - WebmasterWorld

Forum Moderators: open

Message Too Old, No Replies

MySQL fulltext too restrictive

means of searching database

salewit

5:57 am on Nov 20, 2010 (gmt 0)

10+ Year Member

I'm creating a simple private library database system, and I'm having trouble with the search feature. Basically I want just ONE single search box exactly like Google. The problem is I need to do exactly what FULLTEXT is designed to do, but the restrictions are unacceptable for my case. I am on a shared server and can NOT overide the restrictions.

The restrictions I'm talking about are:

4 letter minimum search phrase
Results < 3 are not returned
Results > 50% = none returned

What's the alternative now? I'm trying to search over several fields, so:

$query = "dogs";
$select = "SELECT * from library WHERE title LIKE '%{$query}%' OR author LIKE '%{$query}%' OR subject LIKE '%{$query}%';

This works great! But....

$query = "cats dogs";

Would return nothing even if there were a title called "Cats and Dogs". So if I have to break the words up, and say someone searches for "cats dogs giraffes lions snakes" I'm gonna have an insane SELECT statement. Any ideas? Am I SOL? As I said, I can NOT change the MySQL restrictions (unless it's possible to do on the fly?).

Thanks. Sorry for the long winded post.

topr8

7:08 pm on Nov 21, 2010 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

Top Contributors Of The Month

i'd do something like this:

1). with php strip out the 2 and 3 letter words from the search string and save those words

2). utilise the full text index to search for the 4+ lettered words and use a LEFT JOIN to join the table to an alias of itself and do the LIKE's for the 2/3 letter words.

hope that makes sense.

of course if you don't have 1,000,000's of books in the database, just a few thousand then i'd just use

>>an insane SELECT statement

salewit

8:44 pm on Nov 21, 2010 (gmt 0)

10+ Year Member

That's VERY helpful, and oddly enough I understand it. But we do only have about 2,000 records and expect to have no more than 20,000.

My followup question would be how long can a SELECT statement be? And am I gonna get kicked out of my shared hosting for doing it? It is NOT a busy site. In fact maybe just a dozen queries a day.

Thanks for your help topr8

topr8

8:59 pm on Nov 21, 2010 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

Top Contributors Of The Month

>>My followup question would be how long can a SELECT statement be?

there is a limit, but i can't remember offhand what it will be, you won't come close to it though.

i guess you really do have to only have one search box? couldn't you at least have radio buttons for: author, title, keyword
it would cut down the query quite a bit.

ps. for anyone else reading this, i've realised that there would be a 'fail' situation with my first query, which would be in the situation where there were no four+ letter words entered in the search box, in that instance you'd need to not use the join as zero results would of course be returned from it.