Updating averages?

I'm trying to run a script where I take a value for new + a value for old add them; then average them; then post the average as a new value.


$sql = "UPDATE table SET old=$old+$new, old=avg(old) WHERE id=$id";

I am pretty sure that my problem comes in trying to create a value for $new. I've tried $new= "new" but that didn't work. I'm assuming that I have a null value somewhere in there (which is probably $new)

How do I make $new equal a value that was entered on a previous page? Or if that isn't my problem; could someone help me figure out what is? Thanks in advance.

Why are you setting old twice?

if new is the name of the form field that will be the name of the variable. The other problem is that you don't know how many values have previously been averaged in to old, so you don't have a correct divisor. The easiest way I see to do this is initialize the row with 0 for old and a field 'cnt' with a value of 0. Then you can do this:

update table set old=old+$new, cnt=cnt+1 where id=$id

Then to get the average at any point

select old/cnt as avg from table where id=$id

Notice that old does not have a $ in front of it as you are using the value of old from the table. Also, you can't use avg() the way you are trying as that function is to get the average of a column across several records and needs the group by clause set to know which records to include.