mabus Posted August 25, 2008 Share Posted August 25, 2008 I would like to perform a query on a table (or group of tables) based on multiple parameters. I do not need the results to match all these parameters; matching at least one of the parameters is enough. However, I want to rank the results in such a way that those that match more parameters rank higher than those that match less parameters. Also, I would like to define a "weight" to each of the parameter to help sort the results. For example, say I have a Person table and did a search for: Gender = Male and Eye Color = Blue. Assume I have 4 records: 1. Gender = Male, Eye Color = Blue 2. Gender = Female, Eye Color = Blue 3. Gender = Male, Eye Color = Green 4. Gender = Female, Eye Color = Green The result should give me records #1, #2, and #3. Then let's say that I wanted to give more weight to Gender than Eye Color. Thus, my expected result would be this: #1, #3, and #2. How would I accomplish this? Link to comment https://forums.phpfreaks.com/topic/121226-need-help-for-generating-sql-for-parametric-search/ Share on other sites More sharing options...
fenway Posted August 25, 2008 Share Posted August 25, 2008 For example, say I have a Person table and did a search for: Gender = Male and Eye Color = Blue. ..... The result should give me records #1, #2, and #3. Assuming you mean logical AND and not database AND. If you want to weight it, then just SUM() up successive IF() expressions that give different weights. Link to comment https://forums.phpfreaks.com/topic/121226-need-help-for-generating-sql-for-parametric-search/#findComment-625239 Share on other sites More sharing options...
mabus Posted August 26, 2008 Author Share Posted August 26, 2008 Hey thanks. Can I see some samples of that query? Link to comment https://forums.phpfreaks.com/topic/121226-need-help-for-generating-sql-for-parametric-search/#findComment-625670 Share on other sites More sharing options...
fenway Posted August 26, 2008 Share Posted August 26, 2008 Like this: IF( gender = 'male', 10, 0 ) + IF( eye_color = 'blue', 5, 0 ) Link to comment https://forums.phpfreaks.com/topic/121226-need-help-for-generating-sql-for-parametric-search/#findComment-625985 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.