Hi all,
I have read a ton of posts and googled this upside down, and I cant seem to wrap my brain around this issue.
I have a simple rating system I am building (I know there are a bunch out there, but I am trying to learn it, and I learn by doing better than just reading). I have read bpat1434's post here and read both his tutorials, and I know that I just need to apply his math, that the rating is just (total/#votes).
My challenge is that I am trying to do the math with a single query and maybe that is not possible.
In my rating table, I store the votes per item as such.
ID---VotersID-------ProductID-------Rating----Date
1-------3------------1--------------5--------02-11-2008 1:51:34 PM
2-------3------------2--------------3--------02-11-2008 1:51:34 PM
3-------3------------3--------------2--------02-11-2008 1:51:34 PM
4-------4------------1--------------1--------02-11-2008 3:17:06 PM
5-------4------------2--------------5--------02-11-2008 3:17:06 PM
6-------4------------3--------------3--------02-11-2008 3:17:06 PM
So for the 3 products, I would get the following ratings:
ID#1 (6/2=3)
ID#2 (8/2=4)
ID#3 (5/2=2.5)
The products table is ..
ID---Name
1------The Great Depression
2------Eusebio's Dream
3------Freedom's Sound
So I an using a JOIN in my query, but I stuck using either a COUNT or SUM, but cant figure out how to do both at once so I can do the math with one query and IE on loop.
mysql_select_db ($dbname, $conn) or die (mysql_error());
$query = "SELECT SUM(ratings.Rating), products.Name FROM ratings, products WHERE ratings.ProductID=products.ID GROUP BY products.Name";
$result = mysql_query($query) or die (mysql_error());
while($row = mysql_fetch_array($result)) {
$Count = $row['COUNT(ratings.Rating}'];
$Name = $row['Name'];
$Rating = $row['SUM(ratings.Rating)'];
$Total = $Rating/$Count;
echo 'There is ' .$row['SUM(ratings.Rating)'] . ' rating for ' . $Name . '<br />';
echo 'There is ' . $Count . ' count for ' . $Name . '<br />';
echo 'There is ' . $Total . ' total for ' . $Name . '<br />';
}
But I end up with a SUM value, but not a COUNT value, hence a "Division by zero " warning.
Warning: Division by zero in C:\wamp\www\Don\results.php on line 26
There is 18 rating for Eminent Domain
There is count for Eminent Domain
There is total for Eminent Domain
I know this is simple for many, but I am not the strongest SQL query writer, and I am attempting to learn with the help of those smarter than myself. (like a LOT of people here 🙂 ).
I appreciate the help,
Don
