Friday (day 5)
Constructing SQL statement for the search
Yep, today I need to finish the Profile search for the admin part. As I mentioned, the search is only by fields of experience, so there is no big and scary where clause /especially if you want to cover all cases, like splitting the words with AND, OR etc. will cause huge where with all possible combinations/.
There are 2 specific requirements here: The results need to be ordered by relevance and the report need to show fields of competence which are match to the search criteria next to each row /user/.
User1 – matching fields: PHP, HTML – relevance 2 criteria matched
User2 – matching fields: HTML – relevance: 1 criteria matched
and so on.
At first glance it look like I had to build huge SQL including few sub queries or functions for fetching the fields of relevance and matching fields and I was prepared for at least one day to accomplish this task, but in fact it was very fast.