Best way to code a search engine (across my database)?

[eluser]Aquillyne[/eluser]
I have a few tables and I'd like a user to be able to pull rows from them if his keywords match in certain fields.

Two things:

1) If the user types more than 1 keyword, what logic should I use to give him the best results? Lets assume he's only searching for one thing, of course.

2) How can I account for spelling mistakes? A lot of my rows contain words with apostrophes in them, or Italian words, which might easily be mispelled (e.g. no apostrophe). I want to somehow run a non-literal search, just a close-matching search.

Any ideas?

[eluser]Bramme[/eluser]
1) build a logic that if the term isn't between " ", it searches for LIKE %$key% OR like %$key2% (so explode using the spaces) but if it's between " ", it leaves the space in...

2) you could run an ereg_replace on all your non standard letters, like é à etc. and replace them with e and a.

[eluser]Aquillyne[/eluser]
[quote author="Bramme" date="1216429375"]2) you could run an ereg_replace on all your non standard letters, like é à etc. and replace them with e and a.[/quote]

Problem is, the fields in the database are stored with those letters. I might have, for instance, NAME: "Fooé di Barèbaz". How can I run a query that'll return that result without the query actually involving those special characters?

[eluser]Bramme[/eluser]
Hmm, that's a good question... I suppose you could replace your special characters with a wildcard for one character, but then fooé would match just about anything...

I bet if you Google "php make search engine" you'll eventually find something that has a few valuable tips...

[eluser]Aquillyne[/eluser]
[quote author="Bramme" date="1216475673"]Hmm, that's a good question... I suppose you could replace your special characters with a wildcard for one character, but then fooé would match just about anything...[/quote]

Remind me, how would I do that? Can you run a query for "Foo[?] de Bar[?]baz"?

[eluser]Bramme[/eluser]
I've never done that before, I'd have to look through the mysql documentation for the wildcard...

I'd do it for you, but the page is a little too long to my liking :p
http://dev.mysql.com/doc/refman/5.0/en/s...tions.html


edit: okay, wanted to know anyhow:
http://www.htmlite.com/mysql011.php

So you should replace all your special characters with an underscore and then query the db.

SELECT * FROM 'table' WHERE 'field' LIKE '%foo_ar%'

[eluser]loathsome[/eluser]
I highly recommend using MySQL's FULLTEXT search functions instead of LIKE. FULLTEXT is way quicker and even has built in boolean support.

http://dev.mysql.com/doc/refman/5.0/en/f...earch.html