MySql AVG of table row

[Xephis]

Hi all,

I'm trying to display the average of each row displayed in my table but am having a bit of trouble, any assitance would be appreciated.
Basically the table displays the name, the score you gave them, and their average score. Here is my code below (example, names modified. Just the average of a single player done to test it shows the average) and any assistance would be appreciated.

<?php

$query = "SELECT ROUND(AVG(Rating),2) FROM userrating WHERE Playername LIKE 'player1' ";        
$result = @mysql_query ($query);
$avg = mysql_fetch_array($result);

$userprofile= $_SESSION['name'];
$sql = mysql_query("SELECT * FROM userrating WHERE Ratedby = '$userprofile'");

echo "<table>
        <tr>
        <th> Name </th>
        <th> My Rating </th>
        <th> Average Rating </th>
        </tr>";

 while ($row = mysql_fetch_array($sql)){

        echo "<td align=center>" . $row['Playername'] . "</td>";
        echo "<td align=center>" . $row['Rating'] . "</td>";
        echo "<td align=center>$avg[0]</td>";                     
        echo "</tr>";
        }

 echo "</table>";
?>

[IIM]

Basically the table displays the name, the score you gave them, and their average score. Here is my code below (example, names modified. Just the average of a single player done to test it shows the average) and any assistance would be appreciated.

can you elaborate what result/error you are getting with this....

[Xephis]

There is no error with specific code, it just doesn't do what I want it to. I want it to show the average score of each user in the table next to their name, the code i linked was just to make sure the AVG was working.

at the moment it would display something like the following:

       Name     |   My Rating   | Average Rating   
       -------------------------------------------
       Player1  |       3       |       2
       Player2  |       1       |       2
       Player3  |       4       |       2

Where the Average Rating is just Player1's rating(Player 2 and 3 are displaying player1's average) (as rated by other people, e.g. I rated him as 3, but someone else rated as 1 - so the average is 2.)
I need it to calculate the average rating of each row and can't figure out how to do it.

[urtrivedi]

You may try this code

    <?php
    $userprofile= $_SESSION['name'];
    $sql = mysql_query("SELECT a.Playername, a.Rating,b.avg_rating FROM userrating a left outer join (select Playername,avg(Rating) avg_rating from userrating group by Playername) b on a.Playername=b.Playername WHERE a.Ratedby = '$userprofile'");
    echo "<table>
            <tr>
            <th> Name </th>
            <th> My Rating </th>
            <th> Average Rating </th>
            </tr>";
     while ($row = mysql_fetch_array($sql)){
            echo "<td align=center>" . $row['Playername'] . "</td>";
            echo "<td align=center>" . $row['Rating'] . "</td>";
            echo "<td align=center>" . $row['avg_rating'] . "</td>";                 
            echo "</tr>";
            }
     echo "</table>";
    ?>

[Xephis]

That done the trick, thanks a lot. It's been a while since I have done sql statements, can't believe I forgot something like this.