CODEWITHSUNDEEP
phpmysql
Darpan
Darpan

Reputation: 33

Sort MySQL query result according to best match(number of columns match)

How to sort MySQL query result according to number of columns matched using multiple tables..

select tbl_user.*, tbl_user_personal.*, tbl_user_physical.*, tbl_user_education.*, tbl_user_lifestyle.* 
        from
        tbl_user, tbl_user_personal, tbl_user_physical, tbl_user_education, tbl_user_lifestyle 
        where
        tbl_user.user_id = tbl_user_personal.user_id and 
        tbl_user.user_id = tbl_user_physical.user_id and 
        tbl_user.user_id = tbl_user_education.user_id and 
        tbl_user.user_id = tbl_user_lifestyle.user_id and 
        (
            tbl_user_personal.user_community = 'some-text..' or
            tbl_user_personal.user_sub_caste = 'some-text..' or
            tbl_user_personal.user_marital_status = 'some-text..' or
            tbl_user_personal.user_children = 'some-text..' or
            tbl_user.user_age in ('some-text..', 'some-text..') or
            tbl_user.user_country = 'some-text..' or
            tbl_user_physical.user_height in('some-text..', 'some-text..') or
            tbl_user_physical.user_physical_status = 'some-text..' or
            tbl_user_education.user_education_category = 'some-text..' or
            tbl_user_education.user_occupation = 'some-text..' or
            tbl_user_lifestyle.user_eating_habits = 'some-text..'
        )

Upvotes: 0

Views: 36

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

First, you should learn proper join syntax. Simple rule: Never use commas in the from clause.

But that is not your question. MySQL will treat boolean expressions as numbers in a numeric context, with 1 for true and 0 for false. So, you can just add the values together:

order by ((tbl_user_personal.user_community = 'some-text..') +
          (tbl_user_personal.user_sub_caste = 'some-text..') +
          (tbl_user_personal.user_marital_status = 'some-text..') +
          (tbl_user_personal.user_children = 'some-text..') +
          (tbl_user.user_age in ('some-text..', 'some-text..')) +
          (tbl_user.user_country = 'some-text..') +
          (tbl_user_physical.user_height in('some-text..', 'some-text..')) +
          (tbl_user_physical.user_physical_status = 'some-text..') +
          (tbl_user_education.user_education_category = 'some-text..') +
          (tbl_user_education.user_occupation = 'some-text..') +
          (tbl_user_lifestyle.user_eating_habits = 'some-text..')
         ) desc

You can also put this expression in the select clause, give it a name, and use that for the order by:

order by NumMatches desc;

Upvotes: 1

Pravat Kumar Sahoo
Pravat Kumar Sahoo

Reputation: 303

Use ORDER BY

ORDER BY table1.column_name ASC|DESC, table2.column_name ASC|DESC;

Upvotes: 0

Related Questions