I need a new loop

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\"><font color=\"#000000\">$team_name</font></td>"   


   . "<td align=\"center\" bgcolor=\"#666666\"><font color=\"#000000\">$totalpoints</font></td>"


   . "<td align=\"center\" bgcolor=\"#666666\"><font color=\"#000000\">$t_name</font></td>"


   . "<td align=\"center\" bgcolor=\"#666666\"><font color=\"#000000\">[ <a href=\"modules.php?name=Campaign&amp;file=Reenforce&amp;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.

Posted: Mon Jan 22, 2007 11:37 am

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

registered · 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?

Posted: Mon Jan 22, 2007 6:52 pm

Yes. Totalpoints for each team.

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\"><font color=\"#000000\">$team_name</font></td>"   


   . "<td align=\"center\" bgcolor=\"#666666\"><font color=\"#000000\">$totalpoints</font></td>"


   . "<td align=\"center\" bgcolor=\"#666666\"><font color=\"#000000\">$t_name</font></td>"


   . "<td align=\"center\" bgcolor=\"#666666\"><font color=\"#000000\">[ <a href=\"modules.php?name=Campaign&amp;file=Reenforce&amp;team_id=$team_id\">" . _CLICK . "</a>]</td>"


   . "</tr>";


   }