Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    New to the CF scene
    Join Date
    Jul 2008
    Posts
    6
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Adding up mysql table values

    I have a list of values in a mysql table that I need to add up and find their total. I have tried:

    PHP Code:
     $a array_sum(mysql_query("SELECT value FROM table")); 
    and

    PHP Code:
     $a "SELECT sum(value) FROM table"); 
    Neither of which returned an error, but neither gave the result I wanted. (Errors are enabled on php)

    The script has to work with on two conditions, first it checks if the year is equal to a user selected year. Then gathers all the values for a selected location. From there if the balance is >= 0 it adds them up. At the same time if the balance is <= 0 it adds up a different value in the same row. At the end it needs to add the two values together to be printed later.

    This is what I have now:
    PHP Code:
    if ($fiscal != $fisyr) {
     } else if (
    $loc == "Location") {
       if (
    $balance >= 0) {
        
    $a "SELECT sum(balance) FROM accounts";
        
    $a_res mysql_query($a,$conn) or die(mysql_error());
       } else if (
    $balance <= 0) {
        
    $b "SELECT sum(bbalance) FROM accounts";
            
    $b_res mysql_query($b,$conn) or die(mysql_error());
       }
        
    $dvalue = ($a_res $b_res);

    Last edited by kJasso; 07-30-2008 at 05:44 PM.

  • #2
    Regular Coder
    Join Date
    Aug 2006
    Location
    Richmond, CA
    Posts
    209
    Thanks
    3
    Thanked 11 Times in 10 Posts
    Quote Originally Posted by kJasso View Post
    I have a list of values in a mysql table that I need to add up and find their total. I have tried:

    PHP Code:
     $a array_sum(mysql_query("SELECT value FROM table")); 
    and

    PHP Code:
     $a "SELECT sum(value) FROM table"); 
    Neither of which returned an error, but neither gave the result I wanted. (Errors are enabled on php)

    The script has to work with on two conditions, first it checks if the year is equal to a user selected year. Then gathers all the values for a selected location. From there if the balance is >= 0 it adds them up. At the same time if the balance is <= 0 it adds up a different value in the same row. At the end it needs to add the two values together to be printed later.

    This is what I have now:
    PHP Code:
    if ($fiscal != $fisyr) {
     } else if (
    $loc == "Location") {
       if (
    $balance >= 0) {
        
    $a "SELECT sum(balance) FROM accounts";
        
    $a_res mysql_query($a,$conn) or die(mysql_error());
       } else if (
    $balance <= 0) {
        
    $b "SELECT sum(bbalance) FROM accounts";
            
    $b_res mysql_query($b,$conn) or die(mysql_error());
       }
        
    $dvalue = ($a_res $b_res);

    mysql_query returns a mysql_result. You need to get the data out of the result.

  • #3
    New to the CF scene
    Join Date
    Jul 2008
    Posts
    6
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Tried it as a result and as fetch_array, neither worked. Though I'm probably doing something wrong, I'm fairly new to php coding.

  • #4
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by kJasso View Post
    I have a list of values in a mysql table that I need to add up and find their total. I have tried:

    PHP Code:
     $a array_sum(mysql_query("SELECT value FROM table")); 
    as Daniel Israel said, mysql_query don't return the values. You must use mysql_fetch_assoc, mysql_fetch_array or other methods.

    and
    PHP Code:
     $a "SELECT sum(value) FROM table"); 
    Neither of which returned an error, but neither gave the result I wanted. (Errors are enabled on php)

    The script has to work with on two conditions, first it checks if the year is equal to a user selected year. Then gathers all the values for a selected location. From there if the balance is >= 0 it adds them up. At the same time if the balance is <= 0 it adds up a different value in the same row. At the end it needs to add the two values together to be printed later.

    This is what I have now:
    PHP Code:
    if ($fiscal != $fisyr) {
     } else if (
    $loc == "Location") {
       if (
    $balance >= 0) {
        
    $a "SELECT sum(balance) FROM accounts";
        
    $a_res mysql_query($a,$conn) or die(mysql_error());
       } else if (
    $balance <= 0) {
        
    $b "SELECT sum(bbalance) FROM accounts";
            
    $b_res mysql_query($b,$conn) or die(mysql_error());
       }
        
    $dvalue = ($a_res $b_res);

    the construct have problems, see comments:
    PHP Code:
    if ($fiscal != $fisyr) {
       
    // you are here if $fiscal != $fisyr
     
    } else if ($loc == "Location") {
       
    // you are here if $fiscal == $fisyr
       
    if ($balance >= 0) { // <- $balance must exist and have a value
        
    $a "SELECT sum(balance) FROM accounts";
        
    $a_res mysql_query($a,$conn) or die(mysql_error());
       } else if (
    $balance <= 0) {
        
    $b "SELECT sum(bbalance) FROM accounts";
            
    $b_res mysql_query($b,$conn) or die(mysql_error());
       }
       
    // only one of the blocks of the previous if are executed so
       // $a_res or $b_res must have some previous values
       // which one depend of $balance value
       
    $dvalue = ($a_res $b_res);

    I guess you can use this kind of query:
    PHP Code:
    $query "select sum(balance) as ares, sum(bbalance) as bres from accounts";
    $result mysql_query($query,$conn);
    if(
    $result){
      
    $a_res mysql_result($result,0,'ares');
      
    $b_res mysql_result($result,0,'bres');
      if(
    $a_res && $b_res){
         
    $dvalue $a_res $b_res;
         
    // do something with $dvalue
      
    }
    }else{
      print 
    mysql_error();

    or this:
    PHP Code:
    $query "select (sum(balance) + sum(bbalance)) as dvalue from accounts";
    $result mysql_query($query,$conn);
    if(
    $result){
      
    $dvalue mysql_result($result,0,'dvalue');
      
    // do something with $dvalue
    }else{
      print 
    mysql_error();

    both not tested and I'm not pretty sure about mysql_result since I don't use it often. See the manual.

    regards

  • Users who have thanked oesxyl for this post:

    kJasso (07-31-2008)

  • #5
    New to the CF scene
    Join Date
    Jul 2008
    Posts
    6
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by oesxyl View Post
    I guess you can use this kind of query:
    PHP Code:
    $query "select sum(balance) as ares, sum(bbalance) as bres from accounts";
    $result mysql_query($query,$conn);
    if(
    $result){
      
    $a_res mysql_result($result,0,'ares');
      
    $b_res mysql_result($result,0,'bres');
      if(
    $a_res && $b_res){
         
    $dvalue $a_res $b_res;
         
    // do something with $dvalue
      
    }
    }else{
      print 
    mysql_error();

    both not tested and I'm not pretty sure about mysql_result since I don't use it often. See the manual.

    regards
    That bit works well, and I was able to manipulate it as best as i could to do what i want. But now if there's only 1 value per location it doesnt show the value. I tried using mysql_num_rows but it returned the wrong value.

    I have this now:

    PHP Code:
    //Total Department of Revenue    
                
    $querya "SELECT sum(balance) as ares 
                                     FROM accounts 
                                     WHERE loc='Department of Revenue' && balance >= 0 && fiscalyr ='$fisyr'"
    ;
                
    $resulta mysql_query($querya,$conn);
                
                
    $queryb "SELECT sum(bbalance) as bres 
                                     FROM accounts 
                                     WHERE loc='Department of Revenue' && balance <= 0 && fiscalyr ='$fisyr'"
    ;
                
    $resultb mysql_query($queryb,$conn);
                
            
            if(
    mysql_num_rows($resulta) <= 1) {
                if(
    $balance <= 0) {
                    
    $dvalue $bbalance;
                }else if(
    $balance >= 0) {
                    
    $dvalue $balance;
                }
            }else if(
    mysql_num_rows($resultb) <= 1) {
                if(
    $balance <= 0) {
                    
    $dvalue $bbalance;
                } else if(
    $balance >= 0) {
                    
    $dvalue $balance;
                }
            } else {
                if(
    $resulta && $resultb){
                  
    $a_res mysql_result($resulta,0,'ares');
                     
    $b_res mysql_result($resultb,0,'bres');
                          if(
    $a_res && $b_res){
                             
    $dvalue $a_res $b_res;
                        }else{
                          print 
    mysql_error();
                        }
                }
            }

            
    //Total OPRD
                
    $queryc "SELECT sum(balance) as cres 
                                     FROM accounts 
                                     WHERE loc='OPRD' && balance >= 0 && fiscalyr ='$fisyr'"
    ;
                
    $resultc mysql_query($queryc,$conn);
                
    $queryd "SELECT sum(bbalance) as dres 
                                     FROM accounts 
                                     WHERE loc='OPRD' && balance <= 0 && fiscalyr ='$fisyr'"
    ;
                
    $resultd mysql_query($queryd,$conn);
                
                if(
    $resultc && $resultd){
                  
    $c_res mysql_result($resultc,0,'cres');
                     
    $d_res mysql_result($resultd,0,'dres');
                      if(
    $c_res && $d_res){
                         
    $ovalue $c_res $d_res;
                    }else{
                      print 
    mysql_error();
                    }
                }
                
                
    //Total Collections
                
    $querye "SELECT sum(balance) as eres 
                                     FROM accounts 
                                     WHERE loc='Collections' && balance >= 0 && fiscalyr ='$fisyr'"
    ;
                
    $resulte mysql_query($querye,$conn);
                
    $queryf "SELECT sum(bbalance) as fres 
                                     FROM accounts 
                                     WHERE loc='Collections' && balance <= 0 && fiscalyr ='$fisyr'"
    ;
                
    $resultf mysql_query($queryf,$conn);
                
                if(
    $resulte && $resultf){
                  
    $e_res mysql_result($resulte,0,'eres');
                     
    $f_res mysql_result($resultf,0,'fres');
                      if(
    $e_res && $f_res){
                         
    $cvalue $e_res $f_res;
                    }else{
                      print 
    mysql_error();
                    }
                } 
    The database has this:
    Code:
    +---+-----------------------+----------+---------+
    |id | Location              | Bbalance | balance |
    +---+-----------------------+----------+---------+
    | 1 |Department of Revenue  | 20       | -1      |
    | 2 |Department of Revenue  | 10       | 10      |
    | 3 |Collections            | 30       | -1      |
    | 4 |Collections            | 20       | -1      |
    | 5 |OPRD                   | 10       | -1      |
    | 6 |OPRD                   | 10       | -1      |
    | 7 |OPRD                   | 10       |  5      |
    +---+-----------------------+----------+---------+
    And it outputs this:
    Code:
    +------------------------+-----+
    | Department of Revenue: |5    |
    +------------------------+-----+ 
    | OPRD                   |25   |
    +------------------------+-----+ 
    | Collectons:            |     |
    +------------------------+-----+
    Any ideas why or how to fix it?
    Last edited by kJasso; 07-31-2008 at 10:56 PM.

  • #6
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    let's take only collectons:
    PHP Code:
    //Total Collections
    $querye "SELECT sum(balance) as eres 
                 FROM accounts 
                 WHERE loc='Collections' && balance >= 0 && fiscalyr ='$fisyr'"
    ;
    $resulte mysql_query($querye,$conn);
    $queryf "SELECT sum(bbalance) as fres 
                 FROM accounts 
                 WHERE loc='Collections' && balance <= 0 && fiscalyr ='$fisyr'"
    ;
    $resultf mysql_query($queryf,$conn);
                
    if(
    $resulte && $resultf){
        
    $e_res mysql_result($resulte,0,'eres');
        
    $f_res mysql_result($resultf,0,'fres');
            
    // mysql_result return FALSE if can't retrive the value
            // or the value, so to be sure that if the value is 0 or ''
            // will get a correct result we test
         
    if($e_res !== FALSE && $f_res !== FALSE){
             
    $cvalue $e_res $f_res;
        }else{
                
    // here you can't have a mysql_error because
                // both $resulte and $resultf are true
                
    print "$e_res or $f_res is false";
            }
    }else{
       print 
    mysql_error();

    test this and post results

    regards

  • Users who have thanked oesxyl for this post:

    kJasso (07-31-2008)

  • #7
    New to the CF scene
    Join Date
    Jul 2008
    Posts
    6
    Thanks
    2
    Thanked 0 Times in 0 Posts
    oesxyl you are my hero. That worked perfectly. Thanks a ton for all your help.

  • #8
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by kJasso View Post
    oesxyl you are my hero. That worked perfectly. Thanks a ton for all your help.
    I'm glad it work,

    regards


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •