| MySQL fulltext too restrictive
means of searching database |
salewit
msg:4232655 | 5:57 am on Nov 20, 2010 (gmt 0) |
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
msg:4233092 | 7:08 pm on Nov 21, 2010 (gmt 0) |
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
msg:4233125 | 8:44 pm on Nov 21, 2010 (gmt 0) |
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
msg:4233140 | 8:59 pm on Nov 21, 2010 (gmt 0) |
>>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.
|
|
|
