Hi everyone!
I've been looking for a solution, but i just can't find it anywhere...

Ok, so the problem is:
i have a database which looks something like this:

user_id product_id score
14 . 235 . 79
23 . 235 . 32
53 . 665 . 21
14 . 235 . 90
5 . 675 . 45

What i need to do, is to compare the score, lets say for product_id 235:
user_id 14 gave it 79
user_id 23 gave it 32
user_id 14 gave it 90 points.

I would like to get the difference between votes for every user:
user14 = 79, user23 = 32, so 79 - 32 = 47.

I would really appreciate some help with this, since i have to return my project before the end of the year.

Thanks

Member Avatar
diafol

THis doesn't make much sense, unless you're keeping to at least one user in each combo.
Do you just want the range (max-min)?

If you want to compare 10 different users, you'll have 45 results:

using combinations without repetition:

n!/r!(n-r)! where n = number of users (10) and r = number chosen (2)

You will see that for a 100 users, you'll have 4950 results (combinations).

As you can see, this gets a bit hefty.

Thanks for the reply Ardav!

This is step-by-step of what i need to do:

1. Select user - user_id =343

2. For user_id =343 select all products that he gave score to:
product_id = 43 - score = 99
product_id = 12 - score = 56
product_id = 68 - score = 32
product_id = 124 - score = 67

3. Find all users that voted for the same products e.g.:
for product_id = 43:
user_id = 125 voted 93 points,
user_id = 23 voted 56 points.


4. Calculate the difference between the score of user, so:
for product_id = 43:
user_id = 125 voted 93 points (99 - 93 =6 points of diff.)
user_id = 23 voted 56 points (99 - 56 =43 points of diff.)
return the results....


That's basically what i need to do. I still don't know the number of the users, or product, but it doesn't really matter - i just need to make it work.


Ok, hope someone can help ;)!

Member Avatar
diafol

OK - Here's a version I got to work - but it's not very pretty and it hasn't been thoroughly tested:

SELECT 
  products.productname, 
  users.username,
 (SELECT scores.score FROM scores WHERE scores.user_id = $user_id AND scores.product_id = products.product_id) - scores.score AS total_score 
FROM 
   scores 
INNER JOIN 
   products 
ON 
   scores.product_id = products.product_id 
INNER JOIN 
   users 
ON 
   scores.user_id = users.user_id 
WHERE 
   scores.user_id <> $user_id 
ORDER BY 
   scores.product_id

THIS works if your table structure is like this:

users
user_id (PK/int)
username (varchar)

products
product_id (PK/int)
productname (varchar)

scores
score_id (int/PK/autoincrement - optional field)
product_id (int)
user_id (int)
score (int)

The "total_score" field is actually the 'difference' between scores.

You can then just have a php loop over the ther resultset.

$pn = ""; $output = "";
while($d = mysql_fetch_array($result)){
  if($d['productname'] != $pn){
     $output .= "<h3>{$d['productname']}</h3>";
  }
  $output .= "<p>{$d['productname']}: {$d['total_score']}</p>";
  $pn = $d['productname'];
}

//...
echo $output;