Weighted Search Algorithm for PHP

Question

Problem

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?

Resources

[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);

score 3 · Accepted Answer · answered Sep 19 '11 at 15:51

3

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...

answered Sep 19 '11 at 15:51

Packet Tracer

3,884
4
26
36

I've just tried this but it doesn't actually work - `Unknown column 'w1' in 'field list'` – Sam Sep 19 '11 at 16:06
remove the brackets, and substitue @rx variables for it's real value in php – Packet Tracer Sep 20 '11 at 07:18
it works, try this in your mysql client: select (5 * 4) AS mult ; – Packet Tracer Sep 20 '11 at 07:20

score 1 · Answer 2 · answered Sep 19 '11 at 15:54

just total it up

<?php
require('connect.php') //your db connection data
$redWeight=$_REQUEST['howeverYouPassedTheRedWeighting'];
$blueWeight=$_REQUEST['howeverYouPassedTheBlueWeighting'];
$yellowWeight=$_REQUEST['howeverYouPassedTheYelloWeighting'];
$greenWeight=$_REQUEST['howeverYouPassedTheGreenWeighting'];
$query="SELECT name, ( red*$redWeight+green*$greenWeight+blue*$blueWeight+yellow*$yellowWeight ) AS value FROM items ORDER BY value DESC";
$res=mysql_query($query);
//etc.
?>

score 1 · Answer 3 · answered Sep 19 '11 at 16:00

Sorry but i've not a direct answer. This is a very interesting topic. You can use something related to the euclidean distance, or Pearson correlation. You can find more in books related to Collective Intelligence.

Of course it's more difficult to implement things like these, but your results we'll be much more accurate and precise. I recommend these books:

Algorithms of the Intelligent Web

Programming Collective Intelligence: Building Smart Web 2.0 Applications

score 0 · Answer 4 · answered Sep 19 '11 at 15:55

I'm not sure if I get what you mean right, but how about this:

$user_defined_relevance = array ('red' => 1, 'blue' => 3, 'green' => 2, 'yellow' => 7);
arsort($user_defined_relevance); //this sorts the array by value descending while maintaining index association

$query = 'SELECT * FROM items ORDER BY '.implode(array_keys($user_defined_relevance), ',  DESC');
//will make SELECT * FROM items ORDER BY yellow DESC, blue DESC, green DESC, red DESC;
mysql_query($query);
 ...

Weighted Search Algorithm for PHP

Problem

Resources

4 Answers4