homepage Welcome to WebmasterWorld Guest from 54.205.188.59
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Average, array, multi query.help
Dimhellfire




msg:1580015
 1:56 am on Oct 8, 2005 (gmt 0)

Well i've got a big problem...i've got this table:

stats

StatsID primary autoinc
StatsPlayerID foreign
StatsSeasonID foreign
StatsMatchDate im using it as a 'foreign'
StatsVote
StatsYellow
StatsRed
StatsGoal

Now what i need is the average of each StatsVote based on StatsPlayerID.
Let's figure this:a tournament, where there are many teams and each team has many players.Each match a team plays, every player that...play...gets a mark, a vote.. i need the average for each player i thought i could use AVG() or COUNT but im not going anywhere...

Think that this result will be just a column of a table where other datas are obtained with this query:
$get_players = mysql_query("SELECT DISTINCT
a.PlayerName AS name, a.PlayerTeamID, a.PlayerPositionID,
b.PlayerPositionID, b.PlayerPositionName AS position,
c.OpponentID, c.OpponentName AS team,
a.PlayerPrice AS price
FROM tplls_players AS a
LEFT JOIN tplls_opponents AS c ON (a.PlayerTeamID = c.OpponentID)
LEFT JOIN tplls_playerpositions AS b ON (a.PlayerPositionID = b.PlayerPositionID)
WHERE a.PlayerPositionID = b.PlayerPositionID
AND a.PlayerTeamID = c.OpponentID
AND PlayerSeasonID = 2
ORDER by b.PlayerPositionID", $connection)
or die(mysql_error());

Anyone can help?

 

aspdaddy




msg:1580016
 9:09 am on Oct 8, 2005 (gmt 0)

Does this work? I havent tested it but it looks right .

By player

SELECT PlayerID, SUM(matchvote ) / COUNT(matchvote) as avgVote
GROUP BY PlayerID

Or by palyer and match

SELECT MatchID,PlayerID, SUM(matchvote ) / COUNT(matchvote) as avgVote
GROUP BY MatchID,PlayerID

Dimhellfire




msg:1580017
 1:01 pm on Oct 8, 2005 (gmt 0)

Ok, u gave me a big help^^, by now i changed my first query into this:
$get_players = mysql_query("SELECT DISTINCT
a.PlayerName AS name, a.PlayerTeamID, a.PlayerPositionID,
b.PlayerPositionID, b.PlayerPositionName AS position,
c.OpponentID, c.OpponentName AS team,
a.PlayerPrice AS price,
d.StatsPlayerID, SUM(d.StatsVote) / COUNT(d.StatsVote) as mvoto
FROM tplls_players AS a
LEFT JOIN tplls_opponents AS c ON (a.PlayerTeamID = c.OpponentID)
LEFT JOIN tplls_playerpositions AS b ON (a.PlayerPositionID = b.PlayerPositionID)
LEFT JOIN tplls_stats AS d ON (a.PlayerID = d.StatsPlayerID)
WHERE a.PlayerPositionID = b.PlayerPositionID
AND a.PlayerTeamID = c.OpponentID
AND a.PlayerID = d.StatsPlayerID
AND PlayerSeasonID = 2
GROUP BY d.StatsPlayerID
ORDER by b.PlayerPositionID", $connection)
or die(mysql_error());

$i = 0;
while($data = mysql_fetch_array($get_players))

{ $player[$i] = $data['name'];
$playerid[$i] = $data['id'];
$mvoto[$i] = $data['mvoto'];

mysql_free_result($get_players);

echo "<table width=\"800\" bgcolor=\"$inside_c\" align=\"center\">
<tr>
<td width=\"90\" bgcolor=\"$bg1\" align=\"left\">{$data['name']}<br></td>
<td width=\"90\" bgcolor=\"$bg1\" align=\"center\">{$data['position']}<br></td>
<td width=\"70\" bgcolor=\"$bg1\" align=\"center\">Player Real Team</td>
<td width=\"70\" bgcolor=\"$bg1\" align=\"center\">{$data['price']}<br></td>
<td width=\"30\" bgcolor=\"$bg1\" align=\"center\">Partite<br></td>
<td width=\"30\" bgcolor=\"$bg2\" align=\"center\">($mvoto[$i])<br></td>
<td width=\"30\" bgcolor=\"$bg1\" align=\"center\">Goal fatti/subiti<br></td>
<td width=\"30\" bgcolor=\"$bg1\" align=\"center\">ammo<br></td>
<td width=\"30\" bgcolor=\"$bg1\" align=\"center\">esp<br></td>
<td width=\"330\" bgcolor=\"$bg1\" align=\"center\">vuoto<br></td>
</tr></table>
";
}

Now the problem is i get only 1 line, not a list of players...i believe the problem is in php...but im not able to solve it...anyone can help in that?

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved