ASP Ratings System. "Top 10..."
WizzKidd | Posted 5:30am 23. April 2008 Server Time |
I have recently implemented a rating system into my picture gallery, so that users can rate each picture from 1-5, and it works like a charm.
In a nutshell (without all the fancy bits) I store an ID and the actual URL in one table (tbl_RatingURLs), and then I store the RATING (1-5) and its linking URLID in another table (tbl_RatingVotes).
An example of my data would look like this (this shows that 6 pictures have been voted on, some pictures have been voted several times):
tbl_RatingURLs
==============
ID .... URL ...........................
---------------------------------------
1 ..... gallery.asp?folder=200&pic=3560
2 ..... gallery.asp?folder=200&pic=3561
3 ..... gallery.asp?folder=203&pic=420
4 ..... gallery.asp?folder=206&pic=1620
5 ..... gallery.asp?folder=200&pic=3562
6 ..... gallery.asp?folder=189&pic=32
tbl_RatingVotes
===============
ID .... URLID .... Rating ....
------------------------------
1 ..... 1 ........ 5 .........
2 ..... 1 ........ 4 .........
3 ..... 3 ........ 2 .........
4 ..... 6 ........ 1 .........
5 ..... 4 ........ 1 .........
6 ..... 5 ........ 2 .........
7 ..... 2 ........ 2 .........
8 ..... 1 ........ 5 .........
9 ..... 2 ........ 4 .........
10 .... 6 ........ 1 .........
11 .... 6 ........ 1 .........
12 .... 6 ........ 1 .........
13 .... 6 ........ 1 .........
What I am trying to acheive is to query the (access) database so that I can get:
a) The highest rated picture (so in this case, URLID 1 has the highest rating because it has two 5's and a 4 which totals 9), and I'd also like to show the runners-up, so i'd like to have a table showing the "TOP 10 Highest Rated Pictures"
second ally, another query...
b) The most popular picture (so in this case, URLID 6 has the most votes because it has been voted for 5 times), and i'd also like to show the runners-up, so i'd like to have a table showing the "TOP 10 Popular Pictures".
I hope someone can help me out here, my lack of understanding seems to fall with how I should write the query correctly.
Fingers crossed that haneng is still as helpful as it was 2 years+ ago.
Thanks.
- Neil-One (aka WizzKidd)
- http://www.promotioncity.co.uk |
katy8439 | Posted 6:27am 23. April 2008 Server Time |
Problem A:
SELECT DISTINCT(URLID), SUM(Rating) AS TotalRating FROM TBL_RatingVotes GROUP BY URLID ORDER BY SUM(Rating) DESC
Produces
URLID TotalRating
-----------------------
1 14
2 6
6 5
3 2
5 2
4 1
Problem B)
SELECT DISTINCT(URLID), COUNT(*) AS TotalVotes FROM TBL_RatingVotes GROUP BY URLID ORDER BY COUNT(*) DESC
Produces
URLID TotalVotes
---------------------
6 5
1 3
2 2
3 1
4 1
5 1
Which I think is what you're after
WizzKidd | Posted 7:55am 23. April 2008 Server Time |
Thanks katy8439,
That's almost there... For problems A and B, I'd like to display the actual URL's themselves instead of their URLID. Is this possible?
- Neil-One
WizzKidd | Posted 8:08am 23. April 2008 Server Time |
Oh, one thing...
It appears that your query suggestion would cause a problem because it sorts based on the sum of ALL the ratings. So if a URL gets a vote of 1 a 1000 times it gets a rating of 1000. So the key would be to divide by the number of votes to get an average.
Frett offered a hand and came up with this:
strSQL = strSQL & "SELECT TOP 10 rating_urls.URL AS URL, SUM(rating_votes.Rating)*1.00/COUNT(rating_votes.Rating) AS AverageRating "
strSQL = strSQL & "FROM rating_urls "
strSQL = strSQL & "INNER JOIN rating_votes "
strSQL = strSQL & "ON rating_urls.ID = rating_votes.URLID "
strSQL = strSQL & "GROUP BY rating_urls.URL "
strSQL = strSQL & "ORDER BY SUM(rating_votes.Rating)*1.00/COUNT(rating_votes.Rating) DESC"
The SUM calculation in that query deals with the decimal values when the division takes place.
The problem with this is that it doesnt show just the TOP 10. Any ideas? The result can be seen here: http://www.promotioncity.co.uk/toprating_A.asp
Thanks again.
Neil-One
WizzKidd | Posted 5:00pm 23. April 2008 Server Time |
Sussed it, with a lil help from Alex himself and SQL query guru Frett. Cheers bud.
- Neil-One
Reply to Post ASP Ratings System. "Top 10..."
|
|
|
