String Comparison -- How to return similar strings?

[kjtocool]

I want the user to be able to enter a string, say: "Dog's Play"

 

I have a database filled with various strings like:

 

Dog at Play

Monkey Town

Dog in Play

Doggies Play

John and Sally

Playground

Amy T

Dog World

 

 

What is the best way to utilize PHP and MySQL to return partial and related strings from the database while excluding the irrelevant?

[wildteen88]

MySQL supports Wildcards within queries,

[Rohan Shenoy]

Use the LIKE statement

$sql="SELECT * FROM table_name WHERE filed_name LIKE '%$string%'";

 

If you want to sort the results in terms of relevance to the search string, then have a look at http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html (see for the one Posted by Brad Satoris on December 13 2004 10:14pm in the middle of that page)

[kjtocool]

Thanks, did a little testing, seems like the LIKE statement will help a lot, but it seems to only do a test to see if the items in a specific column contain the string given.  For example, if the column in the database has "Prom" and you do a LIKE search for porm, you will get no results.

 

Is there a simple way to also test for minor misspelling errors like the above?

[dptr1988]

No. the 'LIKE' operater is just about the best the MySQL can offer.

 

If you know how to generate a list of different misspelliings you could list them all in the query like this:

$sql="SELECT * FROM table_name WHERE field_name LIKE '%$string%' OR " .
"field_name LIKE '%$misspelling1%' OR " .
"field_name LIKE '%$misspelling2%' OR " .
"field_name LIKE '%$misspelling3%' OR " .
"field_name LIKE '%$misspelling4%'  ";