CODEWITHSUNDEEP
phpmysqlalgorithmsearchweighted
Sam
Sam

Reputation: 4487

Weighted Search Algorithm for PHP

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

Upvotes: 2

Views: 1701

Answers (4)

santiagobasulto
santiagobasulto

Reputation: 11686

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

Upvotes: 1

cypher
cypher

Reputation: 6992

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

Upvotes: 0

Robot Woods
Robot Woods

Reputation: 5687

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

Upvotes: 1

Packet Tracer
Packet Tracer

Reputation: 3924

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

Upvotes: 3

Related Questions