Best way to build a SMART mySQL & PHP search engine?

What is the best way to build a mySQL & PHP search?

I开发者_C百科 am currently using things like

%term%

I want it to be able to find the result even if they spell it slightly wrong, for example:

Field value = "One: Stop Shop:

They search:

One Shop Stop

OR

One Stop Shop

Etc.. I want a really smart search so they find the information even if they don't search the exact thing.

What is the best way to build a smart search like this?

---

like '%term%' is terrible slow and unoptimized , you might want to add full-text for this column, and use boolean mode for this

Such as

match(column) against('+One +Shop +Stop' in boolean mode)

Take note on the min word lengths is 4, so, you need to consider change it to three, and full-text search only available for myisam

Other opensource search engine like sphinx is ideal for this too

---

Ajreal is right...just thought I'd add an example to help out:

$query = sprintf("SELECT *, 
         MATCH(col1, col2) AGAINST('%s' IN BOOLEAN MODE) AS relevance
     FROM my_table
     ORDER BY relevance DESC LIMIT 20", $keyword);
$rs = $conn->query($query);

Hope this helps

---

You cannot be most efficient by searching on your raw data. This kind of text searching depends on how the data is indexed (this is where Google bot comes in for Google).

So, step 1 is indexing. If your data is in some web pages, you can use standard crawlers available (or even build your own crawler easily, I would suggest python for building crawler). If your data is in some file (not web browsable), then for indexing, you need to write a program to read all data and index them.

Step 2 is searching. Searching approach depend on indexing strategy.

If you are looking for php-mysql based system, review the codes of these projects:

http://www.phpdig.net/

http://sphinxsearch.com/

If you want to know more, search in IEEE Xplore/ACM publications archives. You will get lots of papers published on this topic.

---

You can use the SOUNDEX() function , it's available in both PHP and MYSQL SOUNDEX() with MYSQL

---

Simple, Smart and Secure

As timpng1 put an example on Ajreal Answer above I'd like to make it Secure.

$conn = new mysqli('server', 'username', 'password', 'dbName');
$q = $conn->real_escape_string($_GET["query"]);
$sql = $conn->prepare("SELECT *, MATCH(col1, col2) AGAINST(? IN BOOLEAN MODE) AS relevance FROM my_table ORDER BY relevance DESC LIMIT 20");
$sql->bind_param("s", $q);
$sql->execute();
$rs = $sql->get_result();