CodeIgniter Forums
Best way to code a search engine (across my database)? - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: Best way to code a search engine (across my database)? (/thread-10105.html)



Best way to code a search engine (across my database)? - El Forum - 07-18-2008

[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?


Best way to code a search engine (across my database)? - El Forum - 07-18-2008

[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.


Best way to code a search engine (across my database)? - El Forum - 07-18-2008

[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?


Best way to code a search engine (across my database)? - El Forum - 07-19-2008

[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...


Best way to code a search engine (across my database)? - El Forum - 07-19-2008

[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"?


Best way to code a search engine (across my database)? - El Forum - 07-19-2008

[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/string-comparison-functions.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%'


Best way to code a search engine (across my database)? - El Forum - 07-19-2008

[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/fulltext-search.html