I need a new loop

Donovan · Client Joined: Oct 07, 2003 Posts: 735 Location: Ohio

I have several teams in a table defined by team_id. Each team may have several records in this table with a resource point defined by r_points equaling anywhere from 5 to -5.

Code:

$sql = $db->sql_query("SELECT * FROM " . $prefix . "_tc_ladderteams tclt
JOIN " . $prefix . "_tc_teams tct ON (tclt.team_id = tct.team_id)
LEFT JOIN " . $prefix . "_eto_territories et ON (tct.tid = et.tid)
LEFT JOIN " . $prefix . "_eto_divisions ed ON (tct.div_id = ed.div_id)
JOIN " . $prefix . "_tc_ladders tcl ON (tclt.ladder_id = tcl.sid)
WHERE enabled = 1 AND (ed.div_commander = '$nukeusername' || ed.div_xo ='$nukeusername')
ORDER BY 'name'");

I then need to SUM the resource points for each team and display

Code:

while ( $row = $db->sql_fetchrow($sql) ) {
 $team_id = $row['team_id'];
 $team_name= $row['name'];
 $t_name= $row['t_name'];
$result4 = $db->sql_query ("SELECT SUM(lkup.r_points) as resourcepoints
FROM ".$prefix."_eto_rpoints_lkup lkup
JOIN ".$prefix."_tc_teams tct
WHERE lkup.team_id = tct.team_id
AND (lkup.rp_id = '4' OR lkup.rp_id = '5')");
$info4 = $db->sql_fetchrow($result4);
$totalpoints += $info4['resourcepoints'];

echo"<tr>"
. "<td align=\"center\" bgcolor=\"#666666\">$team_name</td>"
. "<td align=\"center\" bgcolor=\"#666666\">$totalpoints</td>"
. "<td align=\"center\" bgcolor=\"#666666\">$t_name</td>"
. "<td align=\"center\" bgcolor=\"#666666\">[ <a href=\"modules.php?name=Campaign&file=Reenforce&team_id=$team_id\">" . _CLICK . "</a>]</td>"
. "</tr>";
}
echo "</table>"

I know this query works as I've tested it several time.

My problem is displaying all teams in the table listed by teamname, totalpoints, then t_name.

What is happening is the first team may SUM to 3. The next team SUMS to 6 the next to 9, then 12, then 15 and so on.

What I need is some kind of subquery for each teams $totalpoints, but am unsure how to do it.

Donovan · Client Joined: Oct 07, 2003 Posts: 735 Location: Ohio

Still need help figureing this out. Do I need a For Next loop somewhere in the middle to SUM totalpoints for each team?

technocrat · Posted: Mon Jan 22, 2007 6:46 pm

So what you want is if the team name is different start the total points over again?

Donovan · Client Joined: Oct 07, 2003 Posts: 735 Location: Ohio

Yes. Totalpoints for each team.

technocrat · Posted: Tue Jan 23, 2007 10:06 am

So something like

Code:

$previous_team = ''; //<--------
while ( $row = $db->sql_fetchrow($sql) ) {
$team_id = $row['team_id'];
$team_name= $row['name'];
$t_name= $row['t_name'];
$result4 = $db->sql_query ("SELECT SUM(lkup.r_points) as resourcepoints
FROM ".$prefix."_eto_rpoints_lkup lkup
JOIN ".$prefix."_tc_teams tct
WHERE lkup.team_id = tct.team_id
AND (lkup.rp_id = '4' OR lkup.rp_id = '5')");
$info4 = $db->sql_fetchrow($result4);
if ($previous_team != $team_name) {//<--------
$totalpoints = $info4['resourcepoints'];//<--------
$previous_team = $team_name;//<--------
} else {//<--------
$totalpoints += $info4['resourcepoints'];//<--------
}//<--------

echo"<tr>"
. "<td align=\"center\" bgcolor=\"#666666\">$team_name</td>"
. "<td align=\"center\" bgcolor=\"#666666\">$totalpoints</td>"
. "<td align=\"center\" bgcolor=\"#666666\">$t_name</td>"
. "<td align=\"center\" bgcolor=\"#666666\">[ <a href=\"modules.php?name=Campaign&file=Reenforce&team_id=$team_id\">" . _CLICK . "</a>]</td>"
. "</tr>";
}