array output not what I expected - need a review of small code

[xwishmasterx]

I have a problem with the below code:

<?php
$sql_ranks = ("SELECT vtp_members.id, vtp_members.name, vtp_members.teamleader, teams.team_name, count(vtp_tracking.id) surfs
FROM vtp_members, vtp_tracking, teams
WHERE vtp_members.team_id=".$_GET['t']." AND vtp_tracking.credit_members_id=vtp_members.id AND vtp_tracking.action_date > '$last_sunday' AND vtp_tracking.action_date < '$next_sunday'
GROUP BY teams.team_name
ORDER BY surfs DESC");
$rsranks = mysql_query($sql_ranks);
echo "<br><table align='center' valign='top' border='0' width='300px'>
<tr><td colspan='2' align='center'><font size='2px'><b>Team Rankings (Current Week)</b></font></td></tr>
<tr><td><font size='2px'><b>Team</font></td><td align='right'><font size='2px'>Total Surfs</font></td></tr>";
while ($row = mysql_fetch_array($rsranks)) {
echo "<tr><td><font size='2px'><b>".$row[team_name]."</font></td><td align='right'><font size='2px'>".$row[surfs]."</font></td></tr>";}
echo "</table>";
?>

 

Problem is that the last output (".$row[surfs].") is the same for all teams. It seems it is not making a total of all id's and not per team_name.

 

anyone can see what I am doing wrong. I need to sort by team_name and the surfs should display the total of the members with team_id is ".$_GET['t']."

[kickstart]

Hi

 

Are team_name and surfs set up as constants? If not then you need quotes around them when using them as indexes to the $row array.

 

Also you are not joining the teams table to the others. It will just do a cross join, bringing back a silly number of records

 

You should also GROUP BY all non aggregate columns.

 

Something like this is needed (based on a guess of joining vtp_members and team on team_id and id).

 

<?php
$sql_ranks = ("SELECT vtp_members.id, vtp_members.name, vtp_members.teamleader, teams.team_name, count(vtp_tracking.id) surfs
FROM vtp_members
INNER JOIN vtp_tracking ON vtp_tracking.credit_members_id = vtp_members.id 
INNER JOIN teams ON vtp_members.team_id = teams.id 
WHERE vtp_members.team_id=".$_GET['t']." 
AND vtp_tracking.action_date > '$last_sunday' AND vtp_tracking.action_date < '$next_sunday'
GROUP BY vtp_members.id, vtp_members.name, vtp_members.teamleader, teams.team_name
ORDER BY surfs DESC");
$rsranks = mysql_query($sql_ranks);
echo "<br><table align='center' valign='top' border='0' width='300px'>
<tr><td colspan='2' align='center'><font size='2px'><b>Team Rankings (Current Week)</b></font></td></tr>
<tr><td><font size='2px'><b>Team</font></td><td align='right'><font size='2px'>Total Surfs</font></td></tr>";
while ($row = mysql_fetch_array($rsranks)) 
{
echo "<tr><td><font size='2px'><b>".$row['team_name']."</font></td><td align='right'><font size='2px'>".$row['surfs']."</font></td></tr>";
}
echo "</table>";
?> 

 

All the best

 

Keith

[xwishmasterx]

thanks for your answer.

 

I think my problem is I do not fully understand the GROUP function. If I change GROUP to this:

GROUP BY vtp_members.team_id

 

It only outputs one row, allthough there are 3 different values. Why does it group different values?

[xwishmasterx]

Just had to add that the members team id should be same as the teams team id