Re: Counting keywords in database record

> I'm designing a site that is powered by a database, with three main fields, > called "name", "details, and "description". Users enter their search > term in > a field on the search page, and then my PHP script selects all rows from the > DB where that keyword appears in any of the fields. I'd like to be able to > count the number of times the keyword appears in each row, and then sort the > search results by relevance. What is going to be the most efficient way of > doing this? Currently, I'm thinking I'll do this... One possiblity is to maintain a concordance of the words in the database at all times. On each insert/update, you would tear the content apart and update your word-list with which entry that word appears in. In other words, have something like this: content_table ID Name Details Description 1 Fred Whatever This is a description with 'a' twice. 2 Bill Yeah. Another description 'a' in it. word_table Word ContentID Count Fred 1 1 a 1 2 a 2 1 Bill 2 1 . . . You'll need to be sure all updates/inserts rebuild the entries in word_table for that ContentID -- But your search is then all pre-calculated. You could even write a formula giving 10 "points" to having a word in the "Name" and 5 points for the "Details" field and change the raw count to be the result of that instead of just a raw count. For even more fun, you could have the word_table actually point off to an ID, and have multiple words with the same "root" word share an ID. IE, replace the "Word" field above with "WordID" and add: words: Word WordID walk 1 walking 1 run 2 running 2 . . . Search algorithms and techniques is something that looks incredibly simple on the surface, but turns out to be quite complex and intricate underneath -- And what works for one application will not be useful in another. The above is pretty easy (comparatively speaking) and works pretty well for most needs. It would be bad for a situation where the bulk of the data is changing frequently, because the cost of maintaining the concordance is fairly high. But for a search where the bulk of the data stays around for awhile, it's pretty good.