Query not returrning accurate number of occurrences

[coffejor]

Hello All,

 

I have a mySQL query that is created by my php search engine, this query is then used to select the appropriate data.  The sql that my search engine outputs when I search for "test classified ads" and after passing through the stemmer algorithm and removing stop words & symbols is:

 

SELECT DISTINCT COUNT(*) AS occurrences, sdescription, description, location FROM ads WHERE (( sdescription LIKE '%test%' OR description LIKE '%test%' OR location LIKE '%test%') OR( sdescription LIKE '%classified%' OR description LIKE '%classified%' OR location LIKE '%classified%') OR( sdescription LIKE '%ad%' OR description LIKE '%ad%' OR location LIKE '%ad%')) GROUP BY id ORDER BY occurrences DESC 

 

All of this seems to be working fine, but the problem is in how many occurrences of search terms the mySQL database returns.  For a test I created the following examples in the database:

 

id	memberid	category	sdescription	description	location	price
1	123	avionics	Bendix King Radio	Bendix KX-99 NAV/COM with power adapter and intercom pigtails	Fake Town, CA	500.00
2	456	parts	This is a test	This classified is a test, this is only a test	Fake City, MD	150.00
3	789	parts	This is also a test classified ad	Though this test classified ad has more matching terms like test, classified, and ad	Fake County, WI	325.00

 

I would expect it to order the results as row 3 (9 occurrences), row 2 (4 occurrences), and row 1 (0 occurrences), instead my database returns that each has 1 occurrence.  I'm stumped as to why this may be, I think that I have the syntax correct for the mySQL query, but fear that I don't.

 

Any and all advice and insight is very greatly appreciated.

 

~Jordan

[Mchl]

COUNT(*) will not count how many times a word appears in your fields.

[coffejor]

Thanks for the reply.  Do you have any recommendations for how I can determine how many times the search terms appear in the fields?

 

~Jordan

[coffejor]

Ok, I was thinking this over again but maybe my thought logic is flawed.  Wouldn't the query return a result each time it runs into a matching term and therefor when I count the distinct occurrences, it would realize that there were more occurrences of the search terms in certain results?

 

~Jordan

[Mchl]

COUNT() will in general only count rows meeting criteria you provide. For searching occurences in tsxt fields you probably need regular expressions.