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:

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?

Featured Replies in this Discussion

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? ... Replace the red text above with the cigar_id number. To…

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'" .

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'" .

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 :)

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.

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

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????

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.

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! :)