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 4 of 4
  1. #1
    New Coder
    Join Date
    Jul 2012
    Location
    Philippines
    Posts
    35
    Thanks
    5
    Thanked 0 Times in 0 Posts

    What is wrong in summing up the column?

    Code:
    <?php require_once('Connections/connect.php'); ?>
    <?php
      $id_customer = mysql_real_escape_string($_GET['id_customer']);
    
    
    
     $sql_PK = "SELECT * FROM tbl_delivery_details WHERE tbl_customer_id_customer = {$id_customer}";
     $PK = mysql_query($sql_PK, $connect);
     if ( mysql_error() ) {
      die ( mysql_error());
    }
     $row_PK = mysql_fetch_assoc($PK);
    
     $customer_name = $row_PK['tbl_customer_id_customer'];
    
    
    
    $customer_name = mysql_real_escape_string($customer_name);
    
    
    
    $sql = "SELECT 
    	SUM(delivery_details_revenue) as revenue,
    	tbl_customer.customer_name, 
    	tbl_delivery_details.delivery_details_route, 
    	tbl_delivery_details.delivery_details_destination, 
    	tbl_delivery_details.delivery_details_van_no, 
    	tbl_delivery_details.delivery_details_waybill_no, 
    	tbl_delivery_details.delivery_details_charge_invoice,
    	tbl_delivery_details.delivery_details_revenue,
    	tbl_delivery_details.delivery_details_strip_stuff,
    	tbl_delivery_details.delivery_details_date
    
    FROM tbl_customer, tbl_delivery_details 
    
    WHERE tbl_customer.id_customer = tbl_delivery_details.tbl_customer_id_customer 
    AND tbl_customer.id_customer = '{$customer_name}'";
    
    
    $res = mysql_query($sql) or die(mysql_error());
    $row = mysql_fetch_array($res);
    $sum = 0;
    
    
    ?>
    
    
    
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/x	html">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Customer Revenue</title>
    <link rel="stylesheet" type="text/css" href="qcc.css"/>
    </head>
    
    <body>
    
    
    <table border="1">
            <tr>
              <th>Revenue</th>
              
              
            </tr>
            <?php do { ?>
              <tr>
              	
                <td><?php echo $row_PK['delivery_details_revenue'];?></td>
                
               
              </tr>
        
              
              <?php } while ($row_PK = mysql_fetch_assoc($PK));?>
    		 
             <?php  
    			while ($arr=mysql_fetch_array($res)){  
    			$sum += $arr['delivery_details_revenue']; 
    			}?> 
              
                   
          </table>
          
    TOTAL: <?php echo $sum; ?> <br/>
    
    TOTAL:<?php echo $row_PK['revenue'];?>
    </body>
    </html>

    i cant display the sum of all the revenue how to properly do it?

  • #2
    Regular Coder hinch's Avatar
    Join Date
    Sep 2005
    Location
    UK
    Posts
    923
    Thanks
    25
    Thanked 80 Times in 80 Posts
    you are doing fetch_array twice on the same result set?

    $row = mysql_fetch_array($res);

    and

    while ($arr=mysql_fetch_array($res)){
    $sum += $arr['delivery_details_revenue'];
    }

    also you'd be better off calculating it all prehtml and then in your html all you have to do is spit out the var rather than having the while within the html
    A programmer is just a tool which converts caffeine into code

    My work: http://www.fcsoftware.co.uk && http://www.firstcontactcrm.com
    My hobby: http://www.angel-computers.co.uk
    My life: http://www.furious-angels.com

  • #3
    New Coder
    Join Date
    Jul 2012
    Location
    Philippines
    Posts
    35
    Thanks
    5
    Thanked 0 Times in 0 Posts
    how to do it pre html? i tried doing it on sql code and html that's why there is 2 output i tried both but none of them worked

  • #4
    Regular Coder hinch's Avatar
    Join Date
    Sep 2005
    Location
    UK
    Posts
    923
    Thanks
    25
    Thanked 80 Times in 80 Posts
    something like this though i suspect what you're doing is wrong over all not just code wise.

    you're double looping one table (not required), selecting a sum but then referencing the individual rows rather than just the sum, you're then redoing a manual sum in code (after you've done it in the db) and your displaying purely the customer delivery revenue in the table but the total twice at the bottom from 2 different sources neither of which would provide you with the total anyway.

    I think you need to start again and plan what you want to achieve from the datasets you have available to you.

    PHP Code:
    <?php require_once('Connections/connect.php'); ?>
    <?php
      $id_customer 
    mysql_real_escape_string($_GET['id_customer']);

     
    $sql_PK "SELECT * FROM tbl_delivery_details WHERE tbl_customer_id_customer = {$id_customer}";
     
    $PK mysql_query($sql_PK$connect);
     if ( 
    mysql_error() ) {
      die ( 
    mysql_error());
    }
    $html "";
     while (
    $row_PK mysql_fetch_assoc($PK)) {
        
    //get first itteration vars
        
    $customer_name mysql_real_escape_string($row_PK['tbl_customer_id_customer']);
        
    $deliverydetails $row_PK['delivery_details_revenue']
        
    //build start html
        
    $html .= "<tr><td>$deliverydetails</td></tr>"
        
    //do second query
        
    $sql "SELECT 
        SUM(delivery_details_revenue) as revenue,
        tbl_customer.customer_name, 
        tbl_delivery_details.delivery_details_route, 
        tbl_delivery_details.delivery_details_destination, 
        tbl_delivery_details.delivery_details_van_no, 
        tbl_delivery_details.delivery_details_waybill_no, 
        tbl_delivery_details.delivery_details_charge_invoice,
        tbl_delivery_details.delivery_details_revenue,
        tbl_delivery_details.delivery_details_strip_stuff,
        tbl_delivery_details.delivery_details_date 
        FROM tbl_customer, tbl_delivery_details 
        WHERE tbl_customer.id_customer = tbl_delivery_details.tbl_customer_id_customer 
        AND tbl_customer.id_customer = '{$customer_name}'"
    ;
        
    $res mysql_query($sql) or die(mysql_error());
        
    $sum 0;
        
    //loop and sum
        
    while ($row mysql_fetch_array($res)) {
            
    $sum += $row['revenue']; 
        }
    }
    ?>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/x    html">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Customer Revenue</title>
    <link rel="stylesheet" type="text/css" href="qcc.css"/>
    </head>

    <body>
    <table border="1">
            <tr>
              <th>Revenue</th>
            </tr>
            <?php echo $html;?>
    </table>
          
    TOTAL: <?php echo $sum?> <br/>

    TOTAL:<?php echo $row_PK['revenue'];?>
    </body>
    </html>
    A programmer is just a tool which converts caffeine into code

    My work: http://www.fcsoftware.co.uk && http://www.firstcontactcrm.com
    My hobby: http://www.angel-computers.co.uk
    My life: http://www.furious-angels.com


  •  

    Posting Permissions

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