Storing average in MySQL

[j3rmain3]

Just a quick question.

I have a rating system, which is working fine. Before i decided to store the average system into i used this:
[code]<?php
$average = ($row['fin_rating'] + $row['tech_rating'] + $row['doc_rating']) / 3  ;
?>[/code]

This just stored the average in a variable and i simply showed it on the screen as

[code]<td>$average</td>[/code]

But now i want to store that average in the database. The $average is not being POSTed from anywhere so i cannot use the foreach function or $_POST['average']. The average is created on the fly.

Is there a way i can still use

[code]<?php
$average = ($row['fin_rating'] + $row['tech_rating'] + $row['doc_rating']) / 3  ;
?>[/code]

and get the results stored in the database?

Do i use while -->
[code]
<?php
$query = "select * from doc,fin,tech WHERE doc.id=fin.id AND doc.id=fin.id AND doc.id=tech.id";
$result = mysql_query($query) or die ("ERROR");

while ($row=mysql_fetch_assoc($query){

$average = ($row['fin_rating'] + $row['tech_rating'] + $row['doc_rating']) / 3  ;

//And then somehow turn $average into a form method

foreach ($_POST as $average){
$update_average = "UPDATE doc_data SET average = $average WHERE doc.id = $id";
$result_average = mysql_query($update_average) or die ("ERROR");
}
?>
[/code]

I am also confused on which page im supposed to put this on. On the rating form i am using isset so should i place the script beyond the } else { section or place the script in the file where the overall table is shown.

If you need any coding just say!

Thanks

[Psycho]

Saving an average of data you already have in the database is poor format IMHO. Why save the average when you could easily query the average any time you wanted it:

SELECT ((fin_rating + tech_rating + doc_rating)/3) as average FROM table

[j3rmain3]

I dont think i understand what you mean.

Please excuse me if i say explain this wrong.

The average has not been stored in the DB yet. When the ratings are being submitted i want the average to change. For example if the tech_rating = 3 then the average which should be 1 because (3 + 0 + 0) / 3 = 1. When fin_rating was submitted as 2 then (3 + 2 + 0) / 3 = 1.67. and so on. Its more of an update query so the number in the average keeps on changing.

[Psycho]

What I am saying is that storing the average in the database is not necessary. You can just as easily get the average at any time using a query. If you were to store the average in the database as a new column you would have to query the database to get the value, correct? Something like this:

SELECT avgVal FROM table WHERE bla bla

But, saving the average to the database actually complicates things. Because now you need to make sure that any time you change one of the three individual values that you update the average accordingly. This makes your code more complex and prone to errors. So, instead of querying the database for the average column, just do a query that returns back the average of those three fields on the fly as I proposed in my first response. Then you never need to worry about keeping an "average" field in sync with the individual values because you would always be getting the actual average as it exists for those three fields at any given time.

[Psycho]

For example. This query would return back all the fields PLUS a new field called average with, you guessed it, the average of the three fields you specified:

$query = "SELECT *, ((fin.fin_rating + tech.tech_rating + doc.doc_rating)/3) as average
          FROM doc, fin, tech
          WHERE doc.id=fin.id AND doc.id=tech.id";

[j3rmain3]

Im having trouble implementing this into my coding:

This is how i am using it (its coming out with the wrong outcome so its wrong)
[code]
<?php
$query = "SELECT * FROM doc_data, fin_rate,tech_rate,doc_rate WHERE doc_data.doc_id = fin_rate.doc_id AND doc_data.doc_id = tech_rate.doc_id AND doc_data.doc_id = doc_rate.doc_id ORDER BY doc_data.session,doc_data.idea,doc_data.doc_id ";

$average = "SELECT *,((tech_rate.tech_rating + fin_rate.fin_rating + doc_rate.doc_rating)/3) as average FROM doc_data, doc_rate, fin_rate, tech_rate WHERE doc_data.doc_id=fin_rate.doc_id AND doc_data.doc_id=tech_rate.doc_id AND doc_data.doc_id=doc_rate.doc_id";

$r_average = mysql_query($average) or die ("ERROR:Unable to run $average".mysql_error());

$result = mysql_query($query) or die ("ERROR:Unable To Run Query".mysql_error());


while ($row=mysql_fetch_assoc($result)){
//this is the calculation which is used to work out the average off all the ratings
#$average = ($row['fin_rating'] + $row['tech_rating'] + $row['doc_rating']) / 3  ;
//This is the section of the table which displays the results from the dB.
echo "<tr>";
echo "<td><p align=center><font face=Verdana size=-1>".$row['session']."</center></td>";
echo "<td><font face=Verdana size=-1><a href=http://pws.stonehouse.oilfield.slb.com/SHTC/SER/IP-1/August_2006_Brainstorm/$row[url]><font face=Verdana size=-1>".$row['idea']."</td>";
echo "<td><p align=center><font face=Verdana size=-1>".$row['inventor_name']."</p></td>";
echo "<td><p align=center><font face=Verdana size=-1>".$row['tech_rating']."</p></td>";
echo "<td><p align=center><font face=Verdana size=-1>".$row['fin_rating']."</p></td>";
echo "<td><p align=center><font face=Verdana size=-1>".$row['doc_rating']."</p></td>";
echo "<td><p align=center><font face=Verdana size=-1>".sprintf('%01.2f',$r_average)."</p></td>";
#echo "<td><font face=Verdana size=-1>$average</td>";
echo "</tr>";


?>[/code]
The number which appears in the table is not the average. I tested this out by changing the number which the total is being divided by to see if the results changed. But the number did not change.

Here are the ratings which i was testing the script on:
tech_rating = 5;
fin_rating = 4;
doc_rating = 3;

The answer is 4 but i keep on getting 3.

Whats going wrong?

I am making a big fuss about this because i was also hoping to use a sort function on the average field so the client can see the best document.

Sorry about bothering you about this alot, im just having trouble getting this done.
Thanks for your advice though. Much appreciated

[Psycho]

You are not bothering me, but if you were to have fully explained what your pupose was in the beginning we could have reached a solution much faster.

You do not need two queries. The second query has everything from the first query PLUS the average. The "average" that you are displaying will be incorrect because you are only looping throug the values in your first query, but you are trying to display values from the second.

Anyway, ALL you need it ONE query which will get all the values you currently get, get the average, and sort by the average. use the database, don't try to code around it.

Try this code:
[code]<?php

$query = "SELECT *, ((tr.tech_rating + fr.fin_rating + dr.doc_rating)/3) as average
          FROM doc_data dd, fin_rate fr, tech_rate tr, doc_rate dr
          WHERE dd.doc_id=fr.doc_id AND dd.doc_id=tr.doc_id AND dd.doc_id=dr.doc_id
          ORDER BY average";

$result = mysql_query($query) or die ("ERROR:Unable To Run Query".mysql_error());

while ($row=mysql_fetch_assoc($result)){
//this is the calculation which is used to work out the average off all the ratings
#$average = ($row['fin_rating'] + $row['tech_rating'] + $row['doc_rating']) / 3  ;
//This is the section of the table which displays the results from the dB.
echo "<tr>";
echo "<td><p align=center><font face=Verdana size=-1>".$row['session']."</center></td>";
echo "<td><font face=Verdana size=-1><a href=http://pws.stonehouse.oilfield.slb.com/SHTC/SER/IP-1/August_2006_Brainstorm/$row[url]><font face=Verdana size=-1>".$row['idea']."</td>";
echo "<td><p align=center><font face=Verdana size=-1>".$row['inventor_name']."</p></td>";
echo "<td><p align=center><font face=Verdana size=-1>".$row['tech_rating']."</p></td>";
echo "<td><p align=center><font face=Verdana size=-1>".$row['fin_rating']."</p></td>";
echo "<td><p align=center><font face=Verdana size=-1>".$row['doc_rating']."</p></td>";
echo "<td><p align=center><font face=Verdana size=-1>".$row['average']."</p></td>";
echo "</tr>";

?>[/code]

[j3rmain3]

Thanks Very Much Mjdamato.

Its Working Perfectly.

Your help was much appreciated.

J£rmain£  :) :D ;D 8)