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.