homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

MySQL fulltext too restrictive
means of searching database

 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.



 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


 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


 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.

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved