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
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