Solved

Adding up values inside of a while loop

Posted on 2011-03-03
34
372 Views
Last Modified: 2013-12-13
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

0
Comment
Question by:Dada44
  • 15
  • 14
  • 4
  • +1
34 Comments
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 35029118
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
 
LVL 4

Expert Comment

by:dmgroom
ID: 35029119

$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
 

Author Comment

by:Dada44
ID: 35029336
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
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 35029586
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
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 35029600
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
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 35029661
Are the fields in the database of type integer ?
0
 

Author Comment

by:Dada44
ID: 35034405
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
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 35034436
If this is varchar the use
SUM(CAST(points AS INT))
instead of SUM(points)
0
 

Author Comment

by:Dada44
ID: 35034797
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
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 35034801
Did you try the checks I suggested above?
0
 

Author Comment

by:Dada44
ID: 35034813
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
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 35034920
how about: SUM(CAST(points AS SIGNED))
0
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 35034943
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
 

Author Comment

by:Dada44
ID: 35035091
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
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 35035108
SELECT venuesid, SUM(CAST(points AS SIGNED)) as sumpoints from venues where venuesid='".$n."' GROUP BY venuesid

Can you run this query ?
0
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 35035125
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
 

Author Comment

by:Dada44
ID: 35035177
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 35035197
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
 

Author Comment

by:Dada44
ID: 35035274
The ids are the same:
 50
Venue id is '4adcda40f964a5208a3e21e3'

50
Venue id is '4adcda40f964a5208a3e21e3'

Is that a problem?
0
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 35035323
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
 

Author Comment

by:Dada44
ID: 35035399
The same:

50
Venue id is '4adcda40f964a5208a3e21e3'

50
Venue id is '4adcda40f964a5208a3e21e3'
0
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 35035488
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
 

Author Comment

by:Dada44
ID: 35035601

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
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 35035716
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
 

Author Comment

by:Dada44
ID: 35035754
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
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 35035876
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
 

Author Comment

by:Dada44
ID: 35035932
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
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 35035946
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
 

Author Comment

by:Dada44
ID: 35035977
Copied and pasted:
Total points is 50
I has to be 100
:((
0
 

Author Comment

by:Dada44
ID: 35035999
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
 
LVL 34

Accepted Solution

by:
Beverley Portlock earned 500 total points
ID: 35036059
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
 

Author Closing Comment

by:Dada44
ID: 35036113
Amazing!!  thanks a ton for your perseverance and wisdom!!
0
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 35036177
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
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 35036212
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
The viewer will learn how to count occurrences of each item in an array.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now