CODEWITHSUNDEEP
mysqlsearch
Abdullah
Abdullah

Reputation: 3

Mysql search based on user weightage

I want to make a search based on user weightage, forxample...

i have following search parameters...
Location
Age
Gender
Sexual-Orientation
Marital-status
and few others...

requirement is to add the weightage for each parameter, like if user sets weightage as...
location weightage to 10
gender to 5
age to 3

then search results should be more relevant to location less to gender and even lesser to age.

i dont know how to do that!!! kindly tell me how is this possible using mysql.

Upvotes: 0

Views: 467

Answers (3)

Malaiyandi Murugan
Malaiyandi Murugan

Reputation: 393

Most of the search engine oriended sites are use FULL-TEXT-SEARCH. It will be very faster compare to select and LIKE... I have added one example and some links ... I think it will be useful for you... In this full text search have some conditions also...

STEP:1

CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT (title,body)
);

STEP:2

INSERT INTO articles (title,body) VALUES
    ('MySQL Tutorial','DBMS stands for DataBase ...'),
    ('How To Use MySQL Well','After you went through a ...'),
    ('Optimizing MySQL','In this tutorial we will show ...'),
    ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
    ('MySQL vs. YourSQL','In the following database comparison ...'),
    ('MySQL Security','When configured properly, MySQL ...');

STEP:3
Natural Language Full-Text Searches:

SELECT * FROM articles
    WHERE MATCH (title,body) AGAINST ('database');

Boolean Full-Text Searches

SELECT * FROM articles WHERE MATCH (title,body)
     AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);

Go through this links viralpatel.net,devzone.zend.com,sqlmag.com,colorado.edu,en.wikipedia.org

Upvotes: 0

histocrat
histocrat

Reputation: 2381

You can do this fairly easily in mysql, since it treats boolean values as numbers: false is 0 and true is 1. A generated query could look something like

SELECT name from customers 
ORDER BY (location = 'Fort Worth, TX')*10 + (age > 21)*3 DESC 
LIMIT 0,20;

This will create a "score" for each customer. Over-21s in Fort Worth get 13 points, younger people in Fort Worth get 10, over-21's elsewhere get 3, and everyone else gets 0. We sort by this score in descending order so that the best matches are displayed first.

Upvotes: 1

mastazi
mastazi

Reputation: 1672

As far as I know you can't really do that, but you can give an order to the results based on one or more attributes, where the first attribute is the one with the highest priority and the last one is the least important, e.g.:

ORDER BY Location, Gender, Age

Upvotes: 0

Related Questions