Compare rows

[newinphp]

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

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

[newinphp]

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

[newinphp]

Anyone please ?!?

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