xwishmasterx Posted April 11, 2011 Share Posted April 11, 2011 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']." Quote Link to comment https://forums.phpfreaks.com/topic/233349-array-output-not-what-i-expected-need-a-review-of-small-code/ Share on other sites More sharing options...
kickstart Posted April 11, 2011 Share Posted April 11, 2011 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 Quote Link to comment https://forums.phpfreaks.com/topic/233349-array-output-not-what-i-expected-need-a-review-of-small-code/#findComment-1199991 Share on other sites More sharing options...
xwishmasterx Posted April 11, 2011 Author Share Posted April 11, 2011 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? Quote Link to comment https://forums.phpfreaks.com/topic/233349-array-output-not-what-i-expected-need-a-review-of-small-code/#findComment-1200004 Share on other sites More sharing options...
xwishmasterx Posted April 11, 2011 Author Share Posted April 11, 2011 Just had to add that the members team id should be same as the teams team id Quote Link to comment https://forums.phpfreaks.com/topic/233349-array-output-not-what-i-expected-need-a-review-of-small-code/#findComment-1200032 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.