Sorting items by a given rating. bit of a problem...
i'm listing a set of webpages on my website, sorting them by a user-given rating. for each webpage i am storing in a database the number of votes cast, a "score" which is a total of all the ratings (ie if there are votes of 4, 4, and 5, the "Score" is 12), and an average rating (score / number of votes).
original plan was to query the database, returning the list sorted by the score. problem is this, in example:
webpage 1 has one vote of 4 stars
webpage 2 has two votes of 2 stars each
both have the same score. webpage 1 should be listed first tho because it has an average rating of 4 vs 2 for webpage 2.
i thought of a second way, sorting the results by the average rating. this creates a new problem tho:
webpage 1 has 100 votes, all 5 outta 5 stars. one person votes 1 outta 5. let's assume that gives it an average rating of 4.99 (for lack of figuring it out)
webpage 2 has only one vote of 5 outta 5.
webpage 2 is gonna get listed before webpage 1 using this system.
basically, with either method, i would have to have both a primary and a secondary sort method, in order to get correct results. ie by saying in plain terms "sort these pages by average score, with the most voted for pages having priority over less voted for" or "sort these pages by their score, with the highest average score having priority over lower rated pages". is there a way to do this? does PHP/MySQL have a way of returning results sorted by a primary key, but also by a seconday key when two primaries match?
have i even explained it well enough?
Last edited by gnznroses : 06-26-2006 at 10:35 PM.
I know your pain, I have done a ratings system before and its not easy.
The best thing to do is to store all the information in the database, pulling the information out and doing the calculations in the PHP is messy. In the database you should have:
#of votes field,
total score field,
avg score field,
These are updated everytime someone rates something.
If you post the current setup of your database I can probably write the PHP and SQL to get it working.
__________________
I have become death, destroyer of worlds.
yeah, those are the values i'm storing in the database. i just dunno how to query the database and have it return the results sorted by two different keys the way i need it. the names of the databse fields are:
RatingVotes <- number of votes
RatingScore <- score total
Rated <- average
so my current query looks like this:
$query = "SELECT * FROM SitesArchive WHERE Category = '$category' ORDER BY $sort DESC LIMIT $start, $limit";
where $sort in this case is curently set to either RatingScore or Rated. depending on which of the above examples you look at and which of the problems i'm trying to solve.
Just get $sort from the url? e.g. append the links where the user can select what sort order they want with a varibale like below.
page.php?sort=average
page.php?sort=total
then change the query to
$query = "SELECT * FROM SitesArchive WHERE Category = '$category' ORDER BY " . $_GET['sort'] . " DESC LIMIT $start, $limit";
That will only work if you have register globals on though. If you dont you can still use the variable that way. You just need to grab the URL from the browser, remove everything before and including = and your sorted.
Again if you need help I can do, the top solution should work, a lot of host have register globals on so you needent worry.
Its bedtime for me so if you still have problems I will pick it up and give you a hand tomorow.
__________________
I have become death, destroyer of worlds.
oh, yeah i get $sort from the url, i just meant it's set to either RatingScore or Rated for the purposes of this example.
so yeah if you could help me with the primary problem that would be really great.
well what if a site has 95 votes with 5/5 and another has 100 with 5/5 but and a final vote of 4/5 from the 101st person... which should go first? the number of voters is pretty close. You could try grouping the number of votes like first 20 their own cat then 40, and 60 and so on, theeen... go by rank...
they would be very close, but one will be ranked slightly higher, mathmetically. i'm not really concerned with very closely rated apges, but just pages that get grouped close together despite one having for example, 100 positive votes, and another only having 2 or 3.
after not messing with this site since early July i figured i should try to iron out this last kink and open the site up.
anyone have further tips for me? it'd be much appreciated.
Yea, you should also add a 'visits' column to your website. Whenever someone clicks on an item to view its detail, you increment the 'visits' column for that record by one. E.g...
PHP Code:
// First get the current hits of the record in a variable $currHits. Then: $newHits=$currHits+1; $sql="UPDATE table SET hits='$newHits' WHERE id='$id' "; mysql_query($sql) or die(mysql_error());
-Turn off register_globals, fast. If you don't, you'll be hacked soon and come here whining about it.
- Always use mysql_real_escape_string() on the data you get from the url, and always check to make sure that it is actually a column name before putting it in a query.
- Whats the point of showing total score? Average score will do a better job than that.
thanks for the tips. it does count hits actually. and i think i used the mysql_real_escape_string tho i'll double check. not heard of register_globals so i'll check that out too.
Quote:
Whats the point of showing total score? Average score will do a better job than that.
i don't show total score, i only was using that internally. i want to show average score, and in fact i do, but i still haven't figured out how to sort the pages by average score. eg a page with 1 vote of 5 stars getting listed above a site with 99 5-star votes and one 4-star vote.
hmm, that sounds promising. not sure i quite understand, as the GROUP BY is new to me. and it's hard for me to know which words are variable names you're assuming and which are PHP reseved words. here's the database field names i'm using:
RatingVotes <- number of votes
RatingScore <- score total
Rated <- average score
would you care to rewrite the code filling those in?
is the code you posted sorting the returned results in two ways? cause what i really need is sorting it by average score, primarily, with a secondary sort of the number of votes cast.
if that makes sense. hard to put into words, tho i'm sure you get the gist of it.
Last edited by gnznroses : 08-09-2006 at 07:22 PM.
If the totals are already calculated then do the following:
SELECT category, rated, ratingvotes, ratingscore
FROM SitesArchive
WHERE category = '$category'
ORDER BY rated DESC, ratingvotes DESC LIMIT $start, $limit
This query will sort the highest rated (Primary) and highest number of votes (Secondary)
awesome, awesome. thanks imagine and everyone else who gave input :thumbsup:
if you wanna see the site i'm building, it's themicropages.com
i don't think it'll be much of a success -- my plan changed completely while i was working on it -- but it's a learning experience nonetheless.
oh, can someone tell me the difference between this:
SELECT category, rated, ratingvotes, ratingscore FROM
and this:
SELECT * FROM
i'm guessing it's to not allow unwanted database entries to be pulled by hackers, but since the SELECT command only returns the results, which i process inside the PHP script and selectively output only certain fields, not the raw data, i don't see how a hacker could do anything with it. i do understand that i have to check any commands that output results based on user-provided input, like when i'm doing a query and returning entries based on what is in the GET variable (the url), but i sanitize those like this:
function TranslateSort($thesort){
if ($thesort == "rated"){
return "Rated";
}elseif ($thesort == "newest"){
return "DateAdded";
}elseif ($thesort == "popular"){
return "Hits";
}else{
// don't allow sorting we don't use ourselves, eg password (also prevents injection)
return '';
}
}
oh, can someone tell me the difference between this:
SELECT category, rated, ratingvotes, ratingscore FROM
and this:
SELECT * FROM
Has nothing to do with hackers. It's database optimization. If you have 10 columns and only need 3 why bring back all 10 fields of data if you are not using them? It's not a big deal with a small database with minimal traffic. Get a large database with lots of traffic and you will see a performance difference.
Also, when you do a slect * the database must figure out the column names before it brings back the data. If you specify them it's slightly faster. Once again it all depends on how big the database is.
It's always good to practice optimized techniques regardless of having a big or small database.
Sorting items by a given rating. bit of a problem...
