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

## Recommended Answers

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

## All 4 Replies

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

Anyone please ?!?

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;``````
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.