Topic: Simple column addition and storing the computed value

[junkalam]

I asked this question in another forum but didn't get a reply. Maybe it's a dumb question but i'm kind of stuck.

Here is my simple scenario. my beginner application stores test results as shown in the table below. Each section_id is for a section of the test that is worth 25 points total. I want to be able to add the points in the four sections for each user_id to get the total grade out of 100 for that user.

Code: [Select]

result_id      section_id             user_id                 points         
   1                50                    3                      10
   2                60                    3                      20
   3                70                    3                      15
   4                80                    3                      11 

How can i add 10+20+15+11 and store the result in a new column with the relevant user_id?

[sader]

what u mean store result i new column?
if u just wont get total sum of point of user 3
then just use this query
"SELECT SUM(points) AS pts FROM table WHERE user_id=3"

after fetching result u will have total sum in $row['pts']

[junkalam]

thanks for the reply.

Is there a way to automate this query for each user_id? I mean as soon as the 4 points fields are populated the sum is automatically calculated and stored in 'pts'

[sader]

well it looks like u need create new field on players table lets say `pts`

then before u are inserting data into table that u showed do some checking quory like this

$check_result = mysql_query("SELECT COUNT(*) AS counter, SUM(points) AS total_pts  FROM stats WHERE user_id=3");
$row = mysql_fetch_assoc($check_result);
if($row['counter'] >= 4)
{
//if we have four entries in `stats` we should store total points somewhere before inserting new fight stats
mysql_query("UPDATE players SET pts = pts + ".$row['total_pts']." WHERE id=3 LIMIT 1");

//also I think u should remove old entries at this point
mysql_query("DELETE FROM stats WHERE user_id=3");
}

//now do what ever u was doing before I mean store new result into table that I called `stats`

[junkalam]

thanks for the help