I posted this question a qhile back in the PHP boards but I didnt get a workable response so I figured I would give it a better shot, hopefully I can give a better explanation of my problem.

I am trying to make a search function using php that searches through one column of a db to find a match. I have it working fine so far but only if the user spells the name correctly.

Now I have it set both terms to uppercase and I tried %LIKE% but there must be a more effective way. Like how to most big sites search through databases, or even the webmaster-talk forums (im assuming VBulletin). On the php forums they suggested using phonetics but Im just thinking that there must be something else out there.

Searching on google I havent been able to find anything of substance or any premade scripts.

Any suggestions?

assuming MySql;

Maybe use SOUNDEX()

Just about every search I have ever developed has required that the user can spell correctly though.

Originally Posted by Truly

Now I have it set both terms to uppercase and I tried %LIKE% but there must be a more effective way. Like how to most big sites search through databases, or even the webmaster-talk forums (im assuming VBulletin). On the php forums they suggested using phonetics but Im just thinking that there must be something else out there.

Other than near misses ... near vs neer ... what's wrong with the like operator?

It's really a very large topic, how to build a spell check into a search function, try to pull semantic meaning out of the search phrase and the documents in the database, or to try and 'teach' or code a phonetic read. That's probably why you haven't found anything all that helpful ... I don't think you'll find anything concrete, just a few disparate theories to put together.

One thing Google does is, unless you use quotes around a search, break your phrase up into individual words. Search for rabbit food; the #1 result uses both words, but not together.

Most search engines - Endecca, FAST, etc - use a technique called stemming. The word ran could match run, but not errand. Walk could be walking, walker, walks, and so on.

Then you've got abbreviations, which are usually domain specific. A financial search is more likely to match CO to company than Colorado, for example.

hmmm ok I guess for now I will just assume people can spell and maybe do the breaking up of search terms with seperate searches later with soundex.

I think stemming is a little complicated for me . thanks guys

You can use "LIKE" in your query...
If you for example want the user to enter a keyword to search a text (with is stored in database) you can use LIKE like this:

PHP Code:

$Find = mysql_query("select * from Texts where text LIKE '%$keyword%' "); 


So it will search for texts wich contain the keyword.
You can also use like this:

PHP Code:

// Starts with keyword
$Find = mysql_query("select * from Texts where text LIKE '$sv%'");
// End with keyword
$Find = mysql_query("select * from Texts where text LIKE '%$sv'");
// And the contains wich is given in the first window 


I guess I just never thought about this before, I always assumed there was a premade function that made it easy to search, didnt realize how inefficient it was. You would think that it would be something pre-designed. Maybe its too application specific for the designers of PHP to do that, or maybe thats just down the road.

Any ideas?

Hmm, they can't really integrate a search function, because what should it return?
PHP doesn't know wich tables you have and rows in them...

The best way is to make a search page, with a form where they need to enter a word or something, and let php search the database as I said in my last post. Then with a simple while lus you can give back all the database elements containing that word.

Originally Posted by Truly

I guess I just never thought about this before, I always assumed there was a premade function that made it easy to search, didnt realize how inefficient it was. You would think that it would be something pre-designed. Maybe its too application specific for the designers of PHP to do that, or maybe thats just down the road.

Any ideas?

Soundex is probably as close as it comes. Part is different words on a photo blog vs a news site... But it would be a great market...

I'm working on a site that is entirely based on search.

The main advantage I have is that it is a search for businesses, so most search phrases are going to be either names or types of business - which narrows the field a little.

My search algo is pretty complicated all the same, taking into consideration things like the fact that many words which end in "s" are plural, and may have a singular match in the DB.

So the business may be a Butcher, and the user searches for "Butchers" - to get around this *one* of my techniques is to drop any trailing "s"es and replace them with a wildcard.

Another idea is that mis-spellings such as Macro instead of Makro (the wholesaler) would yield no results - so my algo uses the levenshtein function to compare each word in a search query against all of the words in the index of business names (without duplicates) - to find one which is closest to the user's apparent mis-spelling.

This has it's drawbacks, depending on the size of the word-index - but can be assisted by using SOUNDEX to check that the words also sound the same.

This is the most accurate way I have found to counter mis-spellings without writing vast intellegent stemming and context-reading scripts, although it is a Huuuuuuuge subject!

Thanks guys. My head is filled with ideas now, when I get a few things done on what Im working on right now im definetly going to dabble :P