Adding up values inside of a while loop

Question

With the first query in the following code I am looking for the checkings made in Berlin under the game number two.

With the second query I want to give points for each of the checkings.

As you will see I am using the function SUM. But let's say that I have 2 checkings and the points for each checking are 50. Well, instead of echoing 100, with this code I echo 5050

What is wrong with it?

Thanks a lot

THE CODE IS CORRECTED AND WORKING, JUST IN CASE SOMEBODY NEEDS IT. Thanks to all

       $querya = "SELECT * FROM checkins where gamesid=2 and city='Berlin'";
 $resulta = mysql_query($querya) or die(mysql_error());

 $sumOfPoints = 0;
 while($rowa = mysql_fetch_array($resulta)){

      $n = $rowa['venuesid'];
      $queryb = "SELECT venuesid, SUM(points) as sumpoints from venues where venuesid='".$n."' GROUP BY venuesid ORDER BY venuesid";
      $resultb = mysql_query($queryb) or die(mysql_error());

      while($rowb = mysql_fetch_array($resultb)){

           $sumOfPoints +=  $rowb['sumpoints'];

      }

 }


  echo "Total points is $sumOfPoints<br/>";

Why would it echo 100? If you have two checkins, then the outer loop will always fire twice. If anything you might expect 100100. — Lightness Races in Orbit
– Lightness Races in Orbit, Commented Mar 3, 2011 at 16:16
this should be echo out of the while loop or else it will keeps on loops, $rowb = mysql_fetch_array($resultb); echo $rowb['SUM(points)']; — sush
– sush, Commented Mar 3, 2011 at 16:18

Fahim Parkar · Accepted Answer · 2012-05-21 12:42:42Z

1

Some suggestions

Use SUM(points) AS sum (only for clarity when use the sum)
Check that points are numerical fields not varchar.

edited May 21, 2012 at 12:42

Fahim Parkar

31.8k46 gold badges169 silver badges285 bronze badges

answered Mar 3, 2011 at 16:23

Igor Parra

10.4k10 gold badges74 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jenson-button-event · Accepted Answer · 2011-03-03 16:18:53Z

1

points must be a string, you need to cast it

SUM(cast(points) as int)

and then follow Gaurav's point about labeling the compute

answered Mar 3, 2011 at 16:18

jenson-button-event

19k13 gold badges99 silver badges165 bronze badges

Comments

Brian Driscoll · Accepted Answer · 2011-03-03 16:19:00Z

0

You're getting 5050 because your script is echoing 50 in two iterations of the loop.

From the looks of it, your second query's GROUP BY clause is not grouping on venuesid when it should be. So, first try changing your GROUP BY clause to:

GROUP BY venuesid

If that doesn't give you the result you're looking for, then you should try running your query directly against the database with a static value for venuesid to see what you get back from it, then perhaps update your question and we can take it from there.

answered Mar 3, 2011 at 16:19

Brian Driscoll

19.7k3 gold badges51 silver badges66 bronze badges

Collectives™ on Stack Overflow

Adding up values inside of a while loop

3 Answers 3

Some suggestions

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Some suggestions

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related