Adding up values inside of a while loop

Hi all,

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 (an int) 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 (50+50), with this code I echo 5050

What is wrong with it?

Thanks a lot

 

    $querya = "SELECT * FROM checkins where gamesid=2 and city='Berlin'";
    $resulta = mysql_query($querya) or die(mysql_error());
        
        while($rowa = mysql_fetch_array($resulta)){
         $n = $rowa['venuesid'];
          
          $queryb = "SELECT venuesid,SUM(points) from venues where venuesid='$n' GROUP BY venuesid"; 
    	    $resultb = mysql_query($queryb) or die(mysql_error());
    
          $rowb = mysql_fetch_array($resultb);
    	    echo $rowb['SUM(points)'];
    
        }

Open in new window

Dada44Asked:
Who is Participating?
 
Beverley PortlockConnect With a Mentor Commented:
The following code runs here but I had to change cuidad to city

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

Open in new window

0
 
Beverley PortlockCommented:
Try this (changes BOLDED)


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

        while($rowa = mysql_fetch_array($resulta)){
         $n = $rowa['venuesid'];

          $queryb = "SELECT venuesid,SUM(points) AS sumPoints from venues where venuesid='$n' GROUP BY venuesid";
            $resultb = mysql_query($queryb) or die(mysql_error());

          $rowb = mysql_fetch_array($resultb);
            echo $rowb['sumPoints'];

        }
0
 
dmgroomCommented:

$querya = "SELECT * FROM checkins where gamesid=2 and city='Berlin'";
    $resulta = mysql_query($querya) or die(mysql_error());
        
        while($rowa = mysql_fetch_array($resulta)){
         $n = $rowa['venuesid'];
          
          $queryb = "SELECT venuesid,SUM(points) from venues where venuesid='$n' GROUP BY venuesid"; 
    	    $resultb = mysql_query($queryb) or die(mysql_error());
    
          $rowb = mysql_fetch_array($resultb);
    	    $sum += $rowb['SUM(points)'];
    
        }
echo $sum;

Open in new window

0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Dada44Author Commented:
Quite strange.
bportlock, with your code I get no result and with dmgroom's one I get 0

I get 2 records with the first query in phpMyAdmin sql console.
Both records have the same value in venuesid, would that be a problem?

Thanks a lot
0
 
Beverley PortlockCommented:
Let's check how many rows the query returns. After

         $queryb = "SELECT venuesid,SUM(points) from venues where venuesid='$n' GROUP BY venuesid";
              $resultb = mysql_query($queryb) or die(mysql_error());
 
add this

         echo "Number of results is " . mysql_num_rows( $resultb );
0
 
Beverley PortlockCommented:
And while we are ay it, we might as well check what was returned. After

$rowb = mysql_fetch_array($resultb);

add

print_r( $rowb );


0
 
Lukasz ChmielewskiCommented:
Are the fields in the database of type integer ?
0
 
Dada44Author Commented:
I get two results in querya both with the same venuesid value.
This value is a varchar as it contains numbers and letters.

Then in queryb I have 0 results with bportlock code.

But if I substite $n for one of results from querya and I paste queryb in phpMyAdmin SQL chapter I get the venuesid entered and 50 under SUM(points) ..  therefore, It should be worwing, the second loop iteration should obtain another 50 under SUM(points) and they should be added into a 100 ...

Why it is not working? Why I'm getting 0 results in queryb when in my php code?  
Thanks a lot
0
 
Lukasz ChmielewskiCommented:
If this is varchar the use
SUM(CAST(points AS INT))
instead of SUM(points)
0
 
Dada44Author Commented:
It's venuesid the one being a varchar.
I tried your code anyway and it shows: "You have an error in your SQL syntax"  :(
0
 
Beverley PortlockCommented:
Did you try the checks I suggested above?
0
 
Dada44Author Commented:
Yes, I told about it in my previous post:
I get two results (Number of results is 2) in querya both with the same venuesid value.
This value is a varchar as it contains numbers and letters.

Then in queryb I have 0 results with bportlock code. ..
0
 
Lukasz ChmielewskiCommented:
how about: SUM(CAST(points AS SIGNED))
0
 
Beverley PortlockCommented:
If after
          $queryb = "SELECT venuesid,SUM(points) from venues where venuesid='$n' GROUP BY venuesid";

you add
          echo $queryb; exit;

does the query look right? Does have the correct value for $n at this point?
0
 
Dada44Author Commented:
Thanks bportlock, and sorry for the post saying 0 results in queryb it was a typo.
Now it is fixed and I'm back to my first post results:

query a:
Number of results in a is 2

queryb:

Number of results in b is 1
Number of results in b is 1

And if I echo

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

                        echo $rowb['sumpoints'];
                }  


I'm getting in a first iteration 50 and in a second one 50  but not a 100 as I need.
queryb is at this moment:
$queryb = "SELECT venuesid, SUM(points) as sumpoints from venues where venuesid='".$n."' GROUP BY venuesid";

Thanks once more
0
 
Lukasz ChmielewskiCommented:
SELECT venuesid, SUM(CAST(points AS SIGNED)) as sumpoints from venues where venuesid='".$n."' GROUP BY venuesid

Can you run this query ?
0
 
Beverley PortlockCommented:
If you are getting two iterations that means the query is returning two rows.

It could be you need an ORDER BY clause in order to ensure that the data is bunched together correctly for GROUP BY to work. Change the query to be

queryb = "SELECT venuesid, SUM(points) as sumpoints from venues where venuesid='".$n."' GROUP BY venuesid ORDER BY venuesid";

If this does not work then alter the loop to show the data coming back like so

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

                        echo $rowb['sumpoints'];

                         echo "<pre>"; print_r( $rowb ); echo "</pre>";
                }  
0
 
Dada44Author Commented:
Roads_Roads, I can run the query, but same results: 50 and 50 but not adding them.
bportlock, your query obtains also the same results. Here's the print:

50

Array
(
    [0] => 4adcda40f964a5208a3e21e3
    [venuesid] => 4adcda40f964a5208a3e21e3
    [1] => 50
    [sumpoints] => 50
)


50

Array
(
    [0] => 4adcda40f964a5208a3e21e3
    [venuesid] => 4adcda40f964a5208a3e21e3
    [1] => 50
    [sumpoints] => 50
)
0
 
Beverley PortlockCommented:
The venuesid look the same but they must be different. Please modify the loop to run the code below and post the results here


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

                        echo $rowb['sumpoints'];

                         echo "Venue id is '{$rowb['venuesid']}'<br/>";
                }  
0
 
Dada44Author Commented:
The ids are the same:
 50
Venue id is '4adcda40f964a5208a3e21e3'

50
Venue id is '4adcda40f964a5208a3e21e3'

Is that a problem?
0
 
Beverley PortlockCommented:
If the venue id codes are the same then the GROUP BY venuesid should not be returning two results, it should be returning one result. OK, change the above test to be this


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

                        echo $rowb['sumpoints'];

                         echo "Venue id is '". htmlentities( $rowb['venuesid'] ) ."'<br/>";
                }  


and post those results back here please

0
 
Dada44Author Commented:
The same:

50
Venue id is '4adcda40f964a5208a3e21e3'

50
Venue id is '4adcda40f964a5208a3e21e3'
0
 
Beverley PortlockCommented:
Can you dump the VENUES table and post it here? If you can I'll load it into a test database and run the query against it and see if I can make sense of this.

0
 
Dada44Author Commented:

CREATE TABLE IF NOT EXISTS `venues` (
  `venues` int(11) NOT NULL auto_increment,
  `venuesid` varchar(255) default NULL,
  `city` varchar(255) NOT NULL,
  `points` int(11) NOT NULL,
  `gamesid` int(11) NOT NULL,
  PRIMARY KEY  (`venues`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;


INSERT INTO `venues` (`venues`, `venuesid`,  `city`, `points`, `gamesid`) VALUES
(1,  '4b5d66dff964a520e55b29e3', 'Madrid', 500, 1),
(2,  '4bace935f964a520a5183be3', 'NY',  100, 1),
(3,  '4adcda40f964a5208a3e21e3', 'Berlin', 50,  2);
0
 
Beverley PortlockCommented:
Is the original code fragment you posted at the start of this question inside a loop that I cannot see? The table data you have posted has only ONE record for the Berlin venue yet you are expecting (and egtting) 2 rows and a sum of 100 rather than 50.
0
 
Dada44Author Commented:
There are two different tables:

    $querya = "SELECT * FROM checkins where gamesid=2 and city='Berlin'";
   $queryb = "SELECT venuesid,SUM(points) from venues where venuesid='$n' GROUP BY venuesid";

Here's the two rows from this table where gamesid=2 and city= Berlin
(3,  '4adcda40f964a5208a3e21e3', 2, 'Berlin', '2011-03-03 11:42:45'),
(4,  '4adcda40f964a5208a3e21e3', 2, 'Berlin', '2011-03-03 12:06:07'),
0
 
Beverley PortlockCommented:
OK - now it's becoming clear. You need to sum up in the PHP loop. The original question gave the impression that all the summation was to be done in the second query.

            $sumOfPoints = 0;
            while($rowb = mysql_fetch_array($resultb)){

                        $sumOfPoints +=  $rowb['sumpoints'];
                }  
           
            echo "Total points is $sumOfPoints<br/>";


and make sure that second query has an AS clause

SELECT venuesid,SUM(points) AS sumpoints from venues where venuesid='$n' GROUP BY venuesid
0
 
Dada44Author Commented:
Do you mean like the code below? If so the result is:
Total points is 50
Total points is 50


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

        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());
              
              $sumOfPoints = 0;
            while($rowb = mysql_fetch_array($resultb)){
                              
                        $sumOfPoints +=  $rowb['sumpoints'];
                    
                }  
           
            echo "Total points is $sumOfPoints<br/>";
     }
?>

Open in new window

0
 
Beverley PortlockCommented:
Oops.... outside the other loop

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

        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());
              
              $sumOfPoints = 0;
            while($rowb = mysql_fetch_array($resultb)){
                              
                        $sumOfPoints +=  $rowb['sumpoints'];
                    
                }  
           
     }


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

Open in new window

0
 
Dada44Author Commented:
Copied and pasted:
Total points is 50
I has to be 100
:((
0
 
Dada44Author Commented:
I tried this:
http://www.tizag.com/mysqlTutorial/mysqlsum.php
and it is working, but it does not work with my data. Why not? What's different? I cannot see it..
0
 
Dada44Author Commented:
Amazing!!  thanks a ton for your perseverance and wisdom!!
0
 
Beverley PortlockCommented:
The MySQL SUM() function will not work in this instance with the data you have shown me. It is used to group many records' worth of data into a single value, but you are calling one record in two separate iterations of a loop so you have to sum up in the loop, not in MySQL.

You are not using one SQL statement to sum two records at the same time, you are using one SQL statement to sum one record at two different times. If you had used a JOIN query then you would get multiple rows from one query and SUM() would function as you expect

     $querya = "SELECT v.venuesid AS venueId, SUM(points) as sumpoints
                    FROM checkins AS c
                    INNER JOIN venues AS v ON c.venuesid = v.venuesid
                    WHERE c.gamesid=2 AND
                          c.city='Berlin'
                    GROUP BY v.venuesid
                    ORDER BY v.venuesid
                ";
                         
     $resulta = mysql_query($querya) or die(mysql_error());
     $rowa    = mysql_fetch_assoc( $resulta );
     
     echo "Total points is {$rowa['sumpoints']} for venue {$rowa['venueId']}<br/>";

Open in new window

0
 
Beverley PortlockCommented:
Glad you're sorted, but next time it would be good if you could post the sample data in the original post. Once I saw the structure of your data we solved this straightaway. Apart from me putting the counter in the wrong loop, the essence of the problem was solved very quickly.

;-)

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.