Scoring MySQL Results

Hello,

I am working a web site that uses MySQL as it's database. I have a deveopment requirement that calls for the results of a search to be scored by percent of match, i.e. if all criteria match the score is 100, if only half the criteria match the score is 50. The site is bult using php 4.3.x but will soon upgrade to php 5.x.

Is there a query method that can give these results or do I need to accomplish this in php? If I need to use php, what is the best approach? Should I simply query the database for matches using 25% of the criteria and then use php to find the level of matching per row? Or perhaps, query the db multiple times select only a single criteria each time and the search for like rows in each result?

Any help or suggestions would be of great help.


Thanks

What's your actual search look like? When you mention criteria, are you talking about a search on (for example) name, city, state, and zip wherein if only the zip matches it's 25%, if zip and state, it's 50%, and so on?

That's kind of a pain in the ass. I can think of a couple of ways of going about this. One is to do a bunch of different queries, one for each form option specified, and to add result ids/names to an array and keep a count. You'd only add to the array if the item isn't already found in the array. You'd increment a count each time. Then you can compare counts for each match against the count of criteria specified. This is pretty ugly. The other method that springs to mind is to build an array of search criteria, to perform one big assembled query, and for each result, to check all relevant columns against the corresponding search criteria and keep a count that way. This drops you down to one query but is still pretty ugly.

Thanks,

So far, everything I've come up with has been very messy... The best approach seems to be to query the db for all records in the given category. Then use php to step through each record in the result set and calculate the score based on the number of fields and that match the search criteria.

What I'm thinking is something like this....

1. Get the user category, year from, and year to, and query the database using this info.

2. Get all the user entered criteria and place in an array.

3. Create a 2D score array (to include query result row id and score.)

4. Get result row from query results and compare each field with those in the criteria array.

5. Increment the score value for each field value that matches the same criteria.

6. Goto 4 until done...

7. Sort Score array by score

8. Find multiplier to adjust score value (100/num_fields)

9. For number of results per page, loop through score array printing the result row and adjusting the score value.



Tell me what you think? Not very clean but perhaps do-able.....

Seems about right to me.