Calculating Averages with php

[Teng]

Hey Guys,

 

So i have a table with A list of completed activities and each has a score

 

Completed

 

CompletedID (PK)

UserID (FK)

ActivityID (FK)

Score

 

And so i am displaying in a file called UserDetails.php all the user details like Firstname Lastname Phone Address etc. in the format of...

 

$query = "select * from users where UserID = '" . $_SESSION['userid'] ."'";

 

$result = $db_conn->query($query);

$num_results = $result->num_rows;

 

if($num_results > 0 )

{

$row = $result->fetch_assoc();

}

 

?>

 

                            <table border="1" align="center" cellspacing="0" cellpadding="0" bgcolor="#033333">

<tr>

<td width="150" align="center">User Details-</td>

</tr>

                </table>

<hr>

<br />

<table cellspacing="0" cellpadding="10" width="70%">

<tr>

<td><b>Surname:</b></td>

<td><?php echo $row["Surname"]; ?></td>

<td><b>First Name:</b></td>

<td><?php echo $row["Firstname"]; ?></td>

</tr>

 

....etc

                            *Then I want something like

 

                                                  </tr>

                                                              <td><b>Score Average</b></td>

                                                              <td>//CODE FOR SCORE AVERAGE</td>

                                                  </tr>

 

So i'm guessing i am going to have to do another query within the table to select from Completed then select Score and do some kind of for loop for each of the scores linked to the userid? And then somehow add them together and divide by the number of loops? Does that sound right? How do I do this? I'm new to php and not aware of any math functions in it.

[Teng]

I forgot to mention that the ScoreAvg is a field in the user table

 

and when it calculates the average from the Completed table i want it to insert the value into the user table to display in the userdetails.php page. So i guess it would still be the same format as say firstname lastname e.g

 

<table cellspacing="0" cellpadding="10" width="70%">

            <tr>

              <td>Surname:</td>

              <td><?php echo $row["Surname"]; ?></td>

              <td>First Name:</td>

              <td><?php echo $row["Firstname"]; ?></td>

            </tr>

            <tr>

            <td>Score Average:</td>

            </tr>

etc...

 

but have the code up the top of the page to calculate the average and insert it into the user table

 

 

[watthehell]

write this SQL

 

SELECT first_name, last_name, AVG(ScoreAvg) FROM TABLE GROUP BY first_name;

 

[Teng]

This is my select statement atm

 

$query = "select * from users where UserID = '" . $_SESSION['userid'] ."'";

 

There is nothing in the ScoreAvg atm I'm guessing AVG(*) averages that field?

 

The Average i want to get it from another table "Completed" which hold all the scores for each activity. In this table it will hold A UserID linking to a Activity ID and the score for that activity. So i need it to loop through all the activities for a particular user and then give me an average. Once it has done this I want it to insert that data into the User's table in the ScoreAverage field.

[watthehell]

try this hope it works

 

Select u.firstname, u.lastname, AVG(c.scores) from users u, completed c WHERE u.UserID=" . $_SESSION['userid'] ." AND u.USERID = c.ActivityID GROUP BY u.firstname; 

[Teng]

try this hope it works

 

Select u.firstname, u.lastname, AVG(c.scores) from users u, completed c WHERE u.UserID=" . $_SESSION['userid'] ." AND u.USERID = c.ActivityID GROUP BY u.firstname; 

 

hMM

If i do that? It will just average c.score right? I need it to add together all the scores and get an average? Or does it it do that? also at the end u.USERID = c.ACTIVITY id? but these don't match?

[Barand]

"SELECT u.userID, u.firstname, u.lastname, AVG(c.scores)

FROM users u

LEFT JOIN completed c ON u.userID = c.userID

WHERE u.UserID=" . $_SESSION['userid'] .

" GROUP BY u.userID";

[Teng]

OK so atm im trying this

 

$query_avg = "select AVG(Score) as average_score from completed where UserID = '" . $_SESSION['userid'] ."'";

$result_avg = $db_conn->query($query_avg);

$row_avg = $result_avg->fetch_assoc();

 

?>

<tr>

<td><b>Course Avg:</b></td>

<td><?php echo $row_avg['average_score']; ?></td>

</tr>

 

Would this seem like on the right track? It doesnt work atm so obviously its not right suggestions? comments?