php calculating average in db field

issue is when no rating included

[weddingm]

I am trying to figure out how to get average of 2 fields in a database. The issue is that sometimes the visitor can choose to not rate (NR) that particular field. This creates messed up totals.

Below is what I have created so far. The code works great as long as the visitor gives a rating to both fields. Any help appreciated:

$sql3="SELECT * FROM table1 WHERE response >0 AND vendor = \"$x\"";
$result3 = @mysql_query($sql3,$connection) or die("Couldn't connect");
$var3 = 0;
while ($row3 = mysql_fetch_array($result3)) {
$totalrate3=mysql_affected_rows();
$var3+=$row3["response"];
$totalr3 = ($var3 / $totalrate3);}
$sql4="SELECT * FROM table2 WHERE quotable >0 AND vendor = \"$x\"";
$result4 = @mysql_query($sql4,$connection) or die("Couldn't connect");
$var4 = 0;
while ($row4 = mysql_fetch_array($result4)) {
$totalquote4=mysql_affected_rows();
$var4+=$row4["quotable"];
$totalq4 = ($var4 / $totalquote4);}
$grade1='NR';
$score1=($totalr3+$totalq4);
$scoreb1=($score1/2);
if ($scoreb1==1)
{$grade1='F';}
if (($scoreb1==NULL) ¦¦ ($scoreb1<1))
{($grade1='NR') && ($scoreb1='NR');}
if (($scoreb1>1) && ($scoreb1<=2))
{$grade1='D';}
if (($scoreb1>2) && ($scoreb1<=3))
{$grade1='C';}
if (($scoreb1>3) && ($scoreb1<=4))
{$grade1='B';}
if ($scoreb1>4)
{$grade1='A';}

$scoreb1 is the total average of the 2 fields and $grade1 is the average grade of the 2 fields.

[coopster]

Check for a rate value during the loop and perform the average calculations only when there is a rating.

[weddingm]

Could I change

$var3+=$row3["response"];
$totalr3 = ($var3 / $totalrate3);

to

$var3+=$row3["response"];
$vareq=str_replace($var3, '', 'NR');
$totalr3 = ($vareq / $totalrate3);}

I use NR when no rating is given. I want to exclude any NR values from the equation.

Thanks,
Matt

[idfer]

I want to exclude any NR values from the equation.

That usually translates into an "if" statement in programming, so maybe:

if($var3 != 'NR')
 $var3+=$row3["response"];

But then again, in your query you already use "WHERE response > 0", so you shouldn't even fetch rows with response = 'NR'.

BTW, you may find it more helpful to use more descriptive variable names instead of $var3, $totalr3. Also, your while loops have statements that make more sense if they were outside the loop, e.g.:

$var3 = 0; 
[b]$totalrate3=mysql_affected_rows();[/b] 
while ($row3 = mysql_fetch_array($result3)) { 
 $var3+=$row3["response"]; 
}
[b]$totalr3 = ($var3 / $totalrate3);[/b]

[weddingm]

Thanks for the help and suggestion. My new problem is getting the total average rating when a no rating is given. I get the total number of ratings per category by the

if ($totalquote6 !='NR')
{$totalquote6=mysql_affected_rows();}

. However, this doesn't work because someone could give a rating in one category and not in another. This will not give a valid average because a no rating is seen as a row affected.

Any Ideas?

Thanks,
Matt