Search

CoryMathews · 07-04-2008, 08:58 PM

Just wondering if anyone, who has implemented a search, would be willing to share the code behind it.

NOTE: I am looking for more then a simple query using like.

Wildhoney · 07-04-2008, 10:40 PM

How about using full text search, or taking a look at Lucene?

Tanax · 07-04-2008, 10:46 PM

So what "more" are you looking for? A search is just a simple thing..

codefreek · 07-05-2008, 12:38 PM

php tutorial on building a search engines using full text search - Google-sökning

ETbyrne · 07-05-2008, 03:44 PM

This is what I use:
> PHP: similar_text - Manual

See Also:
> PHP similar_text() Function

Rizza · 07-05-2008, 04:58 PM

Quote:

Originally Posted by ETbyrne

This is what I use:
> PHP: similar_text - Manual

See Also:
> PHP similar_text() Function

I would definitely encourage you to find something else to process your searches. The similar_text function is night and day slower than using the database to search with. Even Regex Like in MySQL is faster and that's pretty sad. The problem with using similar_text is...

1. You need to load all data you want into PHP (that means you have HUGE memory overhead)
2. It only accepts strings, which means you're forced to throw it into a loop of some sort.
3. It only returns a percentage value, which means you need to either store another array or replace the key for each array item that you're searching through.
4. Once it's all said and done, you have to sort the array, then pop off the number of items you want.

That's dirty. If you ever have to search for anything over say... 100 rows you'll be paying for it horribly, especially if there are a lot of people that are using that search functionality.

Use Lucene. It takes more setup but its far better than any other options out there and once you've set it up once, you know how to set it up way faster the next time. FULLTEXT is easy, but primitive and has significant downfalls, especially if you have a small indexible database or your indexed content has a lot of the same content.

Jim · 07-05-2008, 05:13 PM

I personally dislike FULL TEXT, the reason for that is because your keyword must be at least 4 keys long.

This gave me problems when a user wanted to find my PHP IRC BOT tutorial and went searching only with "IRC". And people wont keep searching until they found the correct keyword.

The remedy for this problems was lowering the minimal keyword size in the mysql boot config (while starting it from cli), but that must be done as a server admin. So people on shared webhosts arent able to do that themself.

Rizza · 07-05-2008, 05:16 PM

Like I said, there are some significant draw backs. The index must have 3 or more words in the index entry, all words containing 4 or more characters (by default). If you have high word saturation (like my company had an issue with "Orlando" in a project), FULLTEXT will ignore it until there is less total saturation of that word.

CoryMathews · 07-05-2008, 05:31 PM

Well I have spent the last about 3-4 hours looking into this more and more reading about 15 terrible tutorials and lots from the mysql site and have come up with this search method.

It requires a full text search on the fields given so they must varchars, text fields ect.

Anyways heres the query.

Code:

SELECT *,
  MATCH(`FullName`, `Description`, `Category`)
  AGAINST('".$searchString."') AS Relevance 
  FROM `TestTable` 
  HAVING Relevance > 0.2
  ORDER BY Relevance DESC
");

Running it on about 300 rows seems pretty quick still. The Description field is about a paragraph of text and the other 2 are usually just a word or 2.
(27 total, Query took 0.0015 sec)

Thanks for the help guys it pointed me in the right direction. Also if anyone see's any problems with this please let me know.

Ye the 3 character min is going to be a problem and I am on a shared server right now so I guess I will just have to check before searching and tell the user they must input more then 3 characters.