Hey. I am having a problem figuring out how to do a multiple field search in a user database.
Basically, I want to be able to have a search form where one could specify username, age minimum, age maximum, education, or whatever it is.
I know how to do a basic search using SQL LIKE but the problem is that I have multiple fields. So, I figured I can combine many LIKE clauses using OR's since I want people who match only one of the fields to show up in search results also.
But, I also want to assign each search result (user that is found in result that is) a score based on how many LIKE clauses they do match. So, a person who matches all fields would get higher score than one who matches only one field. To do this, would I have to make a query as I described above and then give each user a score by running the result set through some PHP code?
The problems are:
1. What do I do about querying an age RANGE instead of just an age (LIKE would not work here)
Is there a way to assign a score using SQL and then LIMIT the search so I can display only so many results on each page? Because otherwise I would have to make a full SQL query, operate on the full result set, order by score, and then drop most of the results to display only a few - sounds pretty inefficient because each page of results would require a full query.
Is there a problem with the way I plan to chain LIKE clauses using OR?
Thanks a lot!
