How can I SUM these values?

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

I have a "lookup" table that has several positive and negative numbers. I want to SUM these values for each team and display on a page.

Here is an example that is not working.

Code:

while ( $row = $db->sql_fetchrow($sql) ) {


         $team_id = $row['team_id'];


         $team_name= stripslashes($row['name']);


         $t_name= stripslashes($row['t_name']);


$result4 = $db->sql_query ("SELECT SUM(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];


         


    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>"

The page displays and the Points column are all zeros.

Posted: Tue Jan 02, 2007 10:36 am

$totalpoints += $info4['resourcepoints'];

Posted: Tue Jan 02, 2007 11:28 am

Nope

Good catch though. That was a stupid mistake on my part.

Posted: Tue Jan 02, 2007 11:54 am

Run the SQL queries by hand in phpMyAdmin and make sure you are getting the results you expect. You aren't checking to see if $result4 is valid. Perhaps 0 rows were returned.

As an aside, why are you stripslash'ing the strings from the database? You need to do an addslashes() (or mysql_real_escape_string() or equivalent) upon insert, but the slashes are not actually stored in the database table.

Posted: Tue Jan 02, 2007 1:12 pm

Thanks for the help. I have a total of 9 points in the lookup table. 4 points belong to team_id 122, and 5 points belong to team_id 131.

However now in the page team 122 which is the 17th Airborn show all 9 points. The next team is the 2nd Armored Division and they show 18 points but they should show 0 as thier team_id does not exit in the lookup table.

The next is the 2nd Rangers and shows 27 points, but again they should show 0.

It is totaling all points and then adding 9 points to every team.

hmmm?

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'");


$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>";


   }