Welcome to WebmasterWorld Guest from 184.73.3.107

Forum Moderators: open

MySQL fulltext too restrictive

means of searching database

   
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.
7:08 pm on Nov 21, 2010 (gmt 0)

WebmasterWorld Senior Member topr8 is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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
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
8:59 pm on Nov 21, 2010 (gmt 0)

WebmasterWorld Senior Member topr8 is a WebmasterWorld Top Contributor of All Time 10+ Year Member



>>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.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month