PHP Average & Percentages

[jrotunda85]

I have created a table in a mySQL DB where users are going to a form and entering ratings on 6 Categories (5 Specific and 1 Overall) for a particular item_id. On the item table, I have a column that calculates the total number of ratings this particular item_id has received. What I want to do is create a query that calculates out the "average" value; however, I want it to weight things differently. So in essence, I want the final calculation to look like this:

.40 ( Rating 1 + 2 + 3 + 4 + 5) + .60 (Rating 6) / [Total Number of Reviews]

Here's what I've come up with but I am having a problem figuring out how to link the tables. The tables ultimately need to be linked by Cigar_ID but I'm not sure how to pass that variable. If it's any help, my idea was that someone would click on a particular cigar, then be show the rating option so I imagine this could be passed in the session?

<?PHP

//Set Variables
$A = mysql_query("SELECT AVG(Rat_App) FROM Cigar_Reviews WHERE Cigar_ID = $??");
$B = mysql_query("SELECT AVG(Rat_Draw) FROM Cigar_Reviews WHERE Cigar_ID = $??");
$C = mysql_query("SELECT AVG(Rat_Flavor) FROM Cigar_Reviews WHERE Cigar_ID = $??");
$D = mysql_query("SELECT AVG(Rat_Burn) FROM Cigar_Reviews WHERE Cigar_ID = $??");
$E = mysql_query("SELECT AVG(Rat_Finish) FROM Cigar_Reviews WHERE Cigar_ID = $??");
$F = mysql_query("SELECT AVG(Rat_Overall) FROM Cigar_Reviews WHERE Cigar_ID = $??");

//Generate Result
$Result = mysql_query("UPDATE Cigar SET No_Reviews = [0.40 ($A + $B + $C + $D + $E) + ($F)] / No_Reviews WHERE Cigar_ID = $??");

//Print Result
echo ('$Result'); ?>

Any help or guidance you could provide would be much appreciated.

[sudeepjd]

Please let me know if you have already solved the problem.

If I understand your problem correctly, you have a list of Cigars which you want people to rate. When a user clicks on a cigar name, he is taken to another page to rate the cigar on different parameters.

Have you tried passing the cigar_id for a particular cigar through a get variable? <a href="rating_page.php?cigar_id=<cigar_id>">Cigar Name</a> Replace the red text above with the cigar_id number.

To pick up the variable on the next page, use the $Cigar_ID=$_GET['cigar_id']; statement.

Also, Instead of using so many queries to the database each time to get each rating, you can use just one SELECT statement like $sql="SELECT AVG(Rat_App) as a, AVG(Rat_Draw) as b, AVG(Rat_Flavor) as c, AVG(Rat_Burn) as d, AVG(Rat_Finish) as e, AVG(Rat_Overall) as f FROM Cigar_Reviews WHERE Cigar_ID = '$Cigar_ID'" .

You can then pick up the ratings of each using

$result=mysql_query($sql);
$row=mysql_fetch_array($result);
$a=$row['a']; $b=$row['b']; $c=$row['c']; $d=$row['d']; $e=$row['e']; $f=$row['f'];

and then do the required calculations.

Please let me know if this works or if any more help is required.

[jrotunda85]

Please let me know if you have already solved the problem.

If I understand your problem correctly, you have a list of Cigars which you want people to rate. When a user clicks on a cigar name, he is taken to another page to rate the cigar on different parameters.

Have you tried passing the cigar_id for a particular cigar through a get variable? <a href="rating_page.php?cigar_id=<cigar_id>">Cigar Name</a> Replace the red text above with the cigar_id number.

To pick up the variable on the next page, use the $Cigar_ID=$_GET['cigar_id']; statement.

Also, Instead of using so many queries to the database each time to get each rating, you can use just one SELECT statement like $sql="SELECT AVG(Rat_App) as a, AVG(Rat_Draw) as b, AVG(Rat_Flavor) as c, AVG(Rat_Burn) as d, AVG(Rat_Finish) as e, AVG(Rat_Overall) as f FROM Cigar_Reviews WHERE Cigar_ID = '$Cigar_ID'" .

You can then pick up the ratings of each using

$result=mysql_query($sql);
$row=mysql_fetch_array($result);
$a=$row['a']; $b=$row['b']; $c=$row['c']; $d=$row['d']; $e=$row['e']; $f=$row['f'];

and then do the required calculations.

Please let me know if this works or if any more help is required.

I figured out the part of passing the Cigar_ID last night and got that working beautifully using the $_GET function. I will give the second part a try later this evening and I'll let you know if it works. I appreciate the help :)

[jrotunda85]

Alright, I am able to pull the array of averages but the calculation is not working? Here's what I have --

$SQL= "SELECT AVG(RatApp) as a, AVG(RatDraw) as b, AVG(RatFlavor) as c, AVG(RatBurn) as d, AVG(RatFinish) as e, AVG(RatOverall) as f FROM reviews_cigar WHERE CigarID = '$Cigar'";
$result = mysql_query($SQL);
$row = mysql_fetch_array($result);
$a= $row['a']; 
$b=$row['b']; 
$c=$row['c']; 
$d=$row['d']; 
$e=$row['e']; 
$f=$row['f'];
mysql_query("UPDATE cigar SET AverageReview = [(0.40 * ($A + $B + $C + $D + $E)) + (0.60 * ($F))] / NoReviews WHERE CigarID = $Cigar");

[sudeepjd]

Do the calculations before you put the data into the DB. I apologize for not seeing that earlier.

//Get the rating average
$sql= "SELECT AVG(RatApp) as a, AVG(RatDraw) as b, AVG(RatFlavor) as c, AVG(RatBurn) as d, AVG(RatFinish) as e, AVG(RatOverall) as f, COUNT(*) as noreviews FROM reviews_cigar WHERE CigarID = '$Cigar'";
$result = mysql_query($sql);
$row = mysql_fetch_array($result);
$a= $row['a']; 
$b=$row['b']; 
$c=$row['c']; 
$d=$row['d']; 
$e=$row['e']; 
$f=$row['f'];
$noreviews=$row['noreviews'];

//Calculate the Average Rating
$avgReview = ((0.4*($a+$b+$c+$c+$e))+(0.6*$f))/$noreviews;

mysql_query("UPDATE cigar SET AverageReview = '$avgReview' WHERE CigarID = '$Cigar'");

Get PHP to do the calculations and put only the average review into the DB. Instead of doing the calculation in the Update statement itself.

[jrotunda85]

Alright, this seems to be getting a little closer but still not working as expected. I ran it a few times to see and it put in a value on the first go around but has not updated the value despite multiple reviews being added (keep in mind that this part is at the end of the review processing script so it should execute after every review). I had to change one part of the code because the NoReviews is stored in the Cigar table not the review_cigar table.

$SQL= "SELECT AVG(RatApp) as a, AVG(RatDraw) as b, AVG(RatFlavor) as c, AVG(RatBurn) as d, AVG(RatFinish) as e, AVG(RatOverall) as f FROM reviews_cigar WHERE CigarID = '$Cigar'";
$result = mysql_query($SQL);
$row = mysql_fetch_array($result);
$a= $row['a']; 
$b=$row['b']; 
$c=$row['c']; 
$d=$row['d']; 
$e=$row['e']; 
$f=$row['f'];

$SQL2= "SELECT NoReviews from cigar WHERE CigarID = '$Cigar'";
$result2 = mysql_query($SQL2);
$row2 = mysql_fetch_array($result2);
$NoReviews=$row2['NoReviews'];

//Calculate the Average Rating
$avgReview = ((0.4*($a+$b+$c+$c+$e))+(0.6*$f))/$NoReviews;

mysql_query("UPDATE cigar SET AverageReview = '$avgReview' WHERE CigarID = '$Cigar'");

[sudeepjd]

Is it require to store the number of reviews for a cigar when you can directly count them?

Add this line before the //Calculate the Average Rating statement.

echo "$a $b $c $d $e $f $NoReviews";

to check if the avg values and the No Review value are changing as expected. If they are then also echo the $avgReview to see if the calculation is coming out correctly. If this too is correct then try

$sql="UPDATE cigar SET AverageReview = '$avgReview' WHERE CigarID = '$Cigar'";
echo $sql;

Check if the sql statement being printed on screen is correct.

You will find a problem somewhere. If you dont well, then we'll see...

[jrotunda85]

I did what you recommended and it does appear that the calculations and SQL are correct - would there be any issue with how it's being put in the DB. Currently that field is set up as INT with a 5 Character Limit????

[jrotunda85]

I changed the datatype to Decimal (3,3) just to see and the column is changing after each review. The weird thing though is that the average is going down when it should be going up. For example, when I add a review that's all 5, the average should go up but it doesn't, it goes down. Similarly, when I add a review of 1, it goes down as well.

[sudeepjd]

The INT is quite possibly the problem. Change the datatype to a FLOAT and check.

[jrotunda85]

Now here's something interesting, I ran the following query just to see and the value returned is way, way off. It should be 3.x something, it's over 350. What's going on here?

$SQL= "SELECT AVG(RatApp) as a, AVG(RatDraw) as b, AVG(RatFlavor) as c, AVG(RatBurn) as d, AVG(RatFinish) as e, AVG(RatOverall) as f FROM reviews_cigar WHERE CigarID = '$Cigar'";
$result = mysql_query($SQL);
$row = mysql_fetch_array($result);
$a= $row['a']; 
$b=$row['b']; 
$c=$row['c']; 
$d=$row['d']; 
$e=$row['e']; 
$f=$row['f'];

$SQL2= "SELECT NoReviews from cigar WHERE CigarID = '$Cigar'";
$result2 = mysql_query($SQL2);
$row2 = mysql_fetch_array($result2);
$NoReviews=$row2['NoReviews'];

echo "$a $b $c $d $e $f $NoReviews";

$avgCalc = (($a+$b+$c+$d+$e)/5);

echo "$avgCalc";

[sudeepjd]

I can't seem to figure out what's wrong. Is it possible to send me your site files so that I can check them at my end.

[jrotunda85]

I actually figured it out, it was an error in the formula for calculating the value, I was dividing when I should have been adding hence why it wasn't working. As for the weird values, I figured that out too! Thanks so much for the help sudeepdj! :)