Im stuck :(

[chinto]

Hello everyone,

I am working on a project and I am fairly new to PhP etc. What I want to accomplish is to arrive at a total cummlative score by adding scores from a database and then dividing according to how many individual scores there are.

In the database I have a table which holds several values.

Scores Table:

User_id = simply the user that is being scored
overall = overall score ( which is a tally of several other scores)

In this database a particular user will have many "overall" scores on a particular area. So if you browsed the database the raw information may look like the following:

User_id------Overall
74------------85
74------------92
74------------30
74------------20
74------------100
74------------60
74------------72
74------------35
72------------20
72------------85
72------------30
76------------25
76------------90
79------------50
79------------40

What I am looking to accomplish is to pull all the overall scores for user 74, and then add them up and divide them by the total number of overall scores to get an average.

This is my query:

$query = "SELECT overall from scores where User_id = 74";
$result = mysql_query($query);
$num_results = mysql_num_rows($result);

Now I have the information, my question is how do I tally up the scores and arrive at an average. I know this is most likely a very easy task but I have not been able to find the answer as of yet. Any help would be much appreciated.

Thank you in advance,

[jerdo]

Try something like this:

$query = "SELECT overall from scores where User_id = 74"; 
$result = mysql_query($query); 
$num_results = mysql_num_rows($result); 
while ($row = $mysql_fetch_assoc($result))
{
 $total += $row['Overall'];
}
$average = $total / $num_results;

[ratass2002]

SELECT AVG(Overall) FROM Table WHERE User_id=75

[chinto]

SELECT AVG(Overall) FROM Table WHERE User_id=74

Thanks you for your replies. The average in the sql query works great. On the next step of this project I am trying to get this to work in a combine query. For instance, in this situation I would also like to display some information about the user along with their average score. The information about the user is contained in the users table.

As of now I am having difficulty getting the avg() to work in a combine query. The error result is as follows:

Warning: Supplied argument is not a valid MySQL result resource on line 15

Warning: Supplied argument is not a valid MySQL result resource on line 16

The query I am using is as follows:

$query = "SELECT users.User_name, AVG(scores.overall)
from users, scores
where users.User_id = 74 and scores.User_id = 74";

	  $result = mysql_query($query);
                $num = mysql_numrows($result);
                $num_results = mysql_num_rows($result);

                for ($i=0; $i <$num_results; $i++)
							     {
							        $row = mysql_fetch_array($result);
							        echo "<p>".($i+1).".";
							        echo "_______________________________________";
							        echo "<br> User Name: ";
							             echo "<b>";
							             echo htmlspecialchars( stripslashes($row["user_name"]));
							             echo "</b>";
							             echo "<br>";
							             echo "<br> Average Score: ";
							             echo "<b>";
								         echo htmlspecialchars( stripslashes($row["overall"]));
								         echo "</b>";
							             echo "<br>";

 }

My question does "avg()" work in combine queries or do I have to change technique to accomplish this? I am thinking that there is simply something wrong with my query.

In the end, what the fuction will be is to display a list of users sorted by thier average score ranking from highest first to lowest last. So you would come out with something like the following:

Name: Joe Blow
Average Score: 100

Name: Jim Blow
Average Score: 98

Name Jill Blow
Average Score: 74

Thank you in advance,

[Lars_Berg]

$query = "SELECT users.User_name, AVG(scores.overall)
from users inner join scores
on users.User_id = scores.User_id 
where users.user_id = 74
group by users.User_name"; 

You should also have some error handling, i.e. add calls to mysql_error in order to get a better error message.

[sfullman]

Lars pretty much said it, just one further suggestion to use AS for the benefit of your php script

$query = "SELECT users.User_name,

AVG(scores.overall) AS avg

from users inner join scores
on users.User_id = scores.User_id
where users.user_id = 74
group by users.User_name";

that way you can call $row['avg'], or do extract($row) and just use $avg for your variable. Makes things easier.

Sam

[chinto]

Thanks a bunch folks, almost everything is working like a charm now. There is one thing that is still not working for me. When i get the list out I'd like to order is by score. When i do order by scores.overall for some reason its not putting them in descending order.

$query = "SELECT users.User_name, AVG(scores.overall)
from users inner join scores
on users.User_id = scores.User_id
where users.user_id = 74
group by users.User_name
order by scores.overall";

I can get it to order by anything else but not overall score for some reason.

On a sidenote, I have a subscription to orielly is there any books you all would suggest on sql? I have some decent ones for PhP/Mysql but havent found a real good one for sql yet.

[xblue]

you need to order by a value you query.
try using an alias as Sam suggested and order by alias name, like this

$query = "SELECT users.User_name,
AVG(scores.overall) AS average
FROM users INNER JOIN scores
ON users.User_id = scores.User_id
WHERE users.user_id = 74
GROUP BY users.User_name
ORDER BY average DESC";