Query to search parts of a text field

[Anon]

Hello all,

Hopefully someone can help me figure out this small query problem I'm having. I'm in the process of building an app designed to search a classified ads database. The search is being executed against a text field containing a list of keywords, the query comes from a text field filled in by the user.

Current situtation:

The input from the text box is exploded to produce and array of search words. This array is looped through to produce the following query:

SELECT ad_id FROM table_name WHERE keywords LIKE '%$element[0]%' OR keywords LIKE '%element[1]%'........ etc.

Now this works fine and extracts all matching records like you'd expect. What I'd like to do is modify this query to create a count of the number of search words matched, then order by that count and group on the ad_id field (the table's primary key). Unfortunately, I haven't been able to figure out how to accomplish this so a seaches on the words "spot" and "dog" and "spot", "is", and "dog" excuted against a record of "spot is a white dog with black spots" both give a count of 1. Any ideas how to modify this query? Thanx for any help and Season's Greetings to all!!

Cheers,

Geoff A. Virgo

[Anon]

Read the column about "slapping together a search engine"

This works by storing a count of each unique word in a database.
Youll have to modify the code so you only store the total wordcount for each word, and not just each occurence of the word.

In a query you can use that wordcount to weigh your results.

in pseudo code:
select sum(wordcount) as weight from table where word like keyword or word like otherkeyword group by page order by weight desc;

If the word "is" matches, the sum() is increased by the number of times the word "is" accurs in that page. The same would happen for "dog" and "spots",
so the page with the most "spots" and "dogs" and "is" would win