[SOLVED] member rating system

[jaymc]

Im currently designing a member rating system for my website

 

How it works, there are 8 possible ratings you can give someone, for instance

 

* cool

* hansom

* funny

* clever

* ugly

etc

 

The way I have it, when someone rates a member it is saved into a database, if they choose to rate them (cool, clever, ugly) that will insert 3 seperate rows which will look like this

 

Username | Person_Whos_Rating | Cool | Timestamp

Username | Person_Whos_Rating | Clever | Timestamp

Username | Person_Whos_Rating | Ugly | Timestamp

 

Its a pain having to use a row per rating per user, but, I figure to achieve the type of logging where members can see who rated them and at what time, this is the only way...

 

The part that concerns me, on the page where it displays the ratings along with how many they have for each, Im going to have to query the table 8 times, for each rating to compile the total. e.g

 

$coolqyery = "Select `id` FROM `ratings` WHERE `Username` = '$Session' AND `rating` = 'Cool'";

$cleverquery = "Select `id` FROM `ratings` WHERE `Username` = '$Session' AND `rating` = 'clever'";

$uglyquery = "Select `id` FROM `ratings` WHERE `Username` = '$Session' AND `rating` = 'ugly'";

etc

 

Then obviously for each of them, use mysql_num_rows to find the total

 

That would work fine, but querying 8 times for something so simple is really annoying me

 

Currently in my members profile I have about 7 queries in total to pull out all kinds, yet just to output to the rating section Im having to use an aditional 8 queries

 

For what I want, is this how its going to be or can someone think of a better structure or different way of going about this?

 

What needs to remain is a log of every rating which will log the person who rated the member along with the time

 

 

Feedback welcome!!!

[AndyB]

I don't have a solution to your code problem, but I can predict you'll have members fighting each other over the ratings they receive (unless you have very mature members)

[jaymc]

Shame about no code solution, but why the fighting jsut out of curiousty?

 

Anyone else have an idea of how to structure my table or code this, or do you think what I have at the moment is the best way to go about it?

[AndyB]

... but why the fighting jsut out of curiousty?

 

He said I suck. Well you suck too and I just rated you as a zero ... and your grandmother wears army boots .... blah blah blah.

 

Aggregating results depersonalizes the ratings and may help to avoid cliques.

[jaymc]

... but why the fighting jsut out of curiousty?

 

He said I suck. Well you suck too and I just rated you as a zero ... and your grandmother wears army boots .... blah blah blah.

 

Aggregating results depersonalizes the ratings and may help to avoid cliques.

Is that a bad thing though? The more communication and traffic the better?

 

You could call it a social technique

 

Everyone likes a bit of banter, especially over the Internet

 

Depends which way you look at it I suppose. My job is to generate hits, there job is to hit!

[GingerRobot]

To get the numbers of people that thing a person is each type of rating, you can use count(field) and group by(field). For example:

 

<?php
include('test_db_connection.php');
$sql = "SELECT count(`rating`) as `count`, `rating` FROM `ratings` GROUP by `rating` ORDER BY `count` DESC";
$result = mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_assoc($result)){
echo $row['count'].' member(s) think you are '.$row['rating'].'<br />';
}
?>

 

With my test data, this produces:

 

3 member(s) think you are clever
2 member(s) think you are ugly
1 member(s) think you are funny

[jaymc]

Cheers, that worked fine