Help me for Build the Query

[salmanshafiq]

I am working on the wedding site and I am struck on a Search query parameters are

Like age 18 - 25
Profession: Doctor, Engineer, Computer...
Religion: ISLAM, HINDU
Language: English, urd
etc

and need results in following order

100% matches come first
80% matches come after that
50% matches come after that
20% matches come after that

Please help me out for build the Query

[TF_TheMoon]

Sounds like you want to use a Full Text index.

If you're using MySQL http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

[liquorvicar]

I'm not sure if a FULL-TEXT index is the best way to go (it's not an SQL standard and does have a few drawbacks in it's implementation in MySQL).

Assuming your database is properly normalised (i.e. you have a set list of options for each characteristic and they are stored in a separate field) you could do something like:

SELECT p.person_id,
(
IF(profession_id IN (list of professions to search),1,0) +
IF(religion_id IN (list of religions to search),1,0) +
IF(age BETWEEN 18 AND 25,1,0) +
IF(language_id IN (list of languages to search),1,0)
) AS score 
FROM people HAVING score>=1 ORDER BY score DESC

What this should do is calculate a score for each person based on one point if there is a match in any particular category. It should return all results that score 1 or more in descending order of their score. You can easily calculate the score as the maximum score will be 4 (a match for profession, religion, language and age).

[salmanshafiq]

i have already solved this issue thanks for help

solution is :

select *,
case when profession = '{$profession}' then 1 else 0 end
+ case when religion = '{$religion}' then 1 else 0 end
+ case when language = '{$language}' then 1 else 0 end
as score
where
profession = '{$profession}' or
religion = '{$religion}' or

language = '{$language}'

order
by score DESC