coffejor Posted September 22, 2008 Share Posted September 22, 2008 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 Quote Link to comment https://forums.phpfreaks.com/topic/125332-query-not-returrning-accurate-number-of-occurrences/ Share on other sites More sharing options...
Mchl Posted September 22, 2008 Share Posted September 22, 2008 COUNT(*) will not count how many times a word appears in your fields. Quote Link to comment https://forums.phpfreaks.com/topic/125332-query-not-returrning-accurate-number-of-occurrences/#findComment-647818 Share on other sites More sharing options...
coffejor Posted September 22, 2008 Author Share Posted September 22, 2008 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 Quote Link to comment https://forums.phpfreaks.com/topic/125332-query-not-returrning-accurate-number-of-occurrences/#findComment-647828 Share on other sites More sharing options...
coffejor Posted September 22, 2008 Author Share Posted September 22, 2008 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 Quote Link to comment https://forums.phpfreaks.com/topic/125332-query-not-returrning-accurate-number-of-occurrences/#findComment-647854 Share on other sites More sharing options...
Mchl Posted September 22, 2008 Share Posted September 22, 2008 COUNT() will in general only count rows meeting criteria you provide. For searching occurences in tsxt fields you probably need regular expressions. Quote Link to comment https://forums.phpfreaks.com/topic/125332-query-not-returrning-accurate-number-of-occurrences/#findComment-647862 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.