I think I need a different loop

SFDonovan

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. The following query SUMs these values.

$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'");
$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'");
$info4 = $db->sql_fetchrow($result4);
$totalpoints += $info4['resourcepoints'];

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

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

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

Right now the teams are summing to 100 and 200 and 300, so it is adding the sum all over again.

What I need is some kind of subquery but am unsure how to do it.

SFDonovan

bump

I still need help fixing this so it displays correctly.

If I sum the values for any one team they would be 5 or under. It seems that it is suming all points and adding it to each team_id.

Help.

leatherback

Hey Donovan,

Help yourself, and add some formatting to your code. Add space between commands, indent here and there yadieyadie.

I am not familier with the way you query the database (Using the class approach), and as such I am not sure how you loop through the results. In any case:

this: $totalpoints += $info4['resourcepoints']; will add points to the exisitng points; You may need to set that to 0 between teams