I have 5 items in a table [1], each item has 4 attributes (red, green, blue, yellow).
Each attribute can be given a score between 1 and 9 [2].
When performing a search on my website users can specify how relevant each attribute is to the search results by giving each attribute a score between 1 and 9.
What algorithm should I use to calculate and order the results based on the users preference?
[1] - CREATE TABLE items( id INT NOT NULL AUTO_INCREMENT , name VARCHAR(128) , red INT , green INT , blue INT , yellow INT , PRIMARY KEY (id) );
[2] - INSERT INTO items (NAME, red, green, blue, yellow) VALUES ('Random 1', 4, 1, 9, 4), ('Random 2', 1, 1, 2, 9), ('Random 3', 5, 7, 6, 3), ('Random 4', 2, 2, 8, 1);
Calculate the search punctuation as a field and sort the query by it
SELECT *, (red * @rw) AS w1, (green * @gw) AS w2, (blue* @bw) AS w3, (yellow * @yw) AS w4, (w1 + w2 + w3 + w4) AS result
FROM items ORDER BY result DESC;
@rw => red weight, @bw => blue weight, etc...