What is wrong in summing up the column?

juslai · 01-04-2013, 07:39 AM

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?

hinch · 01-04-2013, 09:24 AM

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

juslai · 01-04-2013, 09:57 AM

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

hinch · 01-04-2013, 01:12 PM

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>