adding fields

[jakebur01]

Is this the correct way to do this?

if($num_rows>"0")
{
while($row2 = mysql_fetch_array($result2, MYSQL_ASSOC))
{
$sales1 +=$row2['SALES_OLDEST_PD'];
$sales2 +=$row2['SALES_PD_2'];
$sales3 +=$row2['SALES_PD_3'];
$sales4 +=$row2['SALES_PD_4'];
$sales5 +=$row2['SALES_PD_5'];
$sales6 +=$row2['SALES_PD_6'];
$sales7 +=$row2['SALES_PD_7'];
$sales8 +=$row2['SALES_PD_8'];
$sales9 +=$row2['SALES_PD_9'];
$sales10 +=$row2['SALES_PD_10'];
$sales11 +=$row2['SALES_PD_11'];
$sales12 +=$row2['SALES_PD_12'];
$sales13 +=$row2['SALES_PD_13'];
$sales14 +=$row2['SALES_PD_14'];
$sales15 +=$row2['SALES_PD_15'];
$sales16 +=$row2['SALES_PD_16'];
$sales17 +=$row2['SALES_PD_17'];
$sales18 +=$row2['SALES_PD_18'];
$sales19 +=$row2['SALES_PD_19'];
$sales20 +=$row2['SALES_PD_20'];
$sales21 +=$row2['SALES_PD_21'];
$sales22 +=$row2['SALES_PD_22'];
$sales23 +=$row2['SALES_PD_23'];
$sales24 +=$row2['SALES_PD_24'];
$sales25 +=$row2['SALES_PD_25'];
$sales26 +=$row2['SALES_LAST_PD_26'];
$sales27 +=$row2['SALES_CURR'];
  }

  
  $TOTAL = $sales1 + $sales2 + $sales3 + $sales4 + $sales5 + $sales6 + $sales7 + $sales8 + $sales9 + $sales10 + $sales11 + $sales12 + $sales13 + $sales14 + $sales15 + $sales16 + $sales17 + $sales18 + $sales19 + $sales20 + $sales21 + $sales22 + $sales23 + $sales24 + $sales25 + $sales26 + $sales27;

echo"<br />$TOTAL<br /><br />";

 

Thanks,

 

Jake

[IrOnMaSk]

right now it is not right to do that. if you run it, it will give you the error expecting the '}'. After you close your if statement try to run it... looks like it's going to add up fine...

note: not tested

[jakebur01]

I am getting numbers totaling up in the millions when it should only be 50,000 or less.  I am also getting undefined variable errors on the $Sale1, $Sales2, etc. variables on the first loop.

 

} was left out when I copied the code over.

 

Jake

[jakebur01]

Here is the full code.  Sorry about it not being neat or formatted.  I have three tables.  customers, customer master, customer shipping addresses, and customer sales based on class.  I am querying the customer master then querying the customer sales by class to see if their total is above 500. If it is then the shipping address is retrieved from the shipping address table if their is a address it will insert the line into customers table.

<?php
set_time_limit(900);
ini_set('max_execution_time', '999');

require("../inc/data.inc");



mysql_query('TRUNCATE TABLE customers;');

$query="SELECT * FROM AR_CUST_MAST WHERE ACTIVE='Y' AND CUSTOMER_CLASS<>'RET' AND CUSTOMER_CLASS<>'REF' AND CUSTOMER_CLASS<>'GCA' AND CUSTOMER_CLASS<>'OUT' AND CUSTOMER_CLASS<>'REC' AND CUSTOMER_CLASS<>'SSC' AND CUSTOMER_CLASS<>'WHS' AND CUSTOMER_CLASS<>'SDD'";
echo "$query<br /><br />";
$result = mysql_query($query);

while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
$acct =$row['CUSTOMER_NUM'];
/*
$TOTAL=0;
$SALES1=0;
$SALES2=0;
$SALES3=0;
$SALES4=0;
$SALES5=0;
$SALES6=0;
$SALES7=0;
$SALES8=0;
$SALES9=0;
$SALES10=0;
$SALES11=0;
$SALES12=0;
$SALES13=0;
$SALES14=0;
$SALES15=0;
$SALES16=0;
$SALES17=0;
$SALES18=0;
$SALES19=0;
$SALES20=0;
$SALES21=0;
$SALES22=0;
$SALES23=0;
$SALES24=0;
$SALES25=0;
$SALES26=0;
$SALES27=0;
*/

$query2="SELECT * FROM SA_CUST_ITEM_CLS WHERE ITEM_CLASS = 'YKP' or ITEM_CLASS = 'YKU'or ITEM_CLASS = 'YKT' AND CUSTOMER_NUM='$acct'";
echo "$query2<br /><br />";
$result2 = mysql_query($query2);
$num_rows = mysql_num_rows($result2);
if($num_rows>"0")
{
while($row2 = mysql_fetch_array($result2, MYSQL_ASSOC))
{
$sales1 +=$row2['SALES_OLDEST_PD'];
$sales2 +=$row2['SALES_PD_2'];
$sales3 +=$row2['SALES_PD_3'];
$sales4 +=$row2['SALES_PD_4'];
$sales5 +=$row2['SALES_PD_5'];
$sales6 +=$row2['SALES_PD_6'];
$sales7 +=$row2['SALES_PD_7'];
$sales8 +=$row2['SALES_PD_8'];
$sales9 +=$row2['SALES_PD_9'];
$sales10 +=$row2['SALES_PD_10'];
$sales11 +=$row2['SALES_PD_11'];
$sales12 +=$row2['SALES_PD_12'];
$sales13 +=$row2['SALES_PD_13'];
$sales14 +=$row2['SALES_PD_14'];
$sales15 +=$row2['SALES_PD_15'];
$sales16 +=$row2['SALES_PD_16'];
$sales17 +=$row2['SALES_PD_17'];
$sales18 +=$row2['SALES_PD_18'];
$sales19 +=$row2['SALES_PD_19'];
$sales20 +=$row2['SALES_PD_20'];
$sales21 +=$row2['SALES_PD_21'];
$sales22 +=$row2['SALES_PD_22'];
$sales23 +=$row2['SALES_PD_23'];
$sales24 +=$row2['SALES_PD_24'];
$sales25 +=$row2['SALES_PD_25'];
$sales26 +=$row2['SALES_LAST_PD_26'];
$sales27 +=$row2['SALES_CURR'];
  }

  
  $TOTAL = $sales1 + $sales2 + $sales3 + $sales4 + $sales5 + $sales6 + $sales7 + $sales8 + $sales9 + $sales10 + $sales11 + $sales12 + $sales13 + $sales14 + $sales15 + $sales16 + $sales17 + $sales18 + $sales19 + $sales20 + $sales21 + $sales22 + $sales23 + $sales24 + $sales25 + $sales26 + $sales27;

echo"<br />$TOTAL<br /><br />";
  
  if($TOTAL<"500")
  {
 // do nothing
  }
  else
  {
  
$COMPANY= mysql_real_escape_string($row['COMPANY']); 
  $CUSTOMER_NUM=$row['CUSTOMER_NUM'];
   $CUSTOMER_NAME=mysql_real_escape_string($row['CUSTOMER_NAME']);  
    $ADDRESS_1=mysql_real_escape_string($row['ADDRESS_1']); 
 $ADDRESS_2=mysql_real_escape_string($row['ADDRESS_2']); 
  $ADDRESS_3=mysql_real_escape_string($row['ADDRESS_3']);
   $CITY=mysql_real_escape_string($row['CITY']);
    $STATE=mysql_real_escape_string($row['STATE']); 
	 $ZIP_CODE=mysql_real_escape_string($row['ZIP_CODE']);
		    $PHONE_1=mysql_real_escape_string($row['PHONE_1']);   
			 $PHONE_2=mysql_real_escape_string($row['PHONE_2']);
			  $CONTACT_1=mysql_real_escape_string($row['CONTACT_1']);
			   $CONTACT_2=mysql_real_escape_string($row['CONTACT_2']); 
			    $EMAIL_1=mysql_real_escape_string($row['EMAIL_1']);
				 $EMAIL_2=mysql_real_escape_string($row['EMAIL_2']);
				  $WEBSITE=mysql_real_escape_string($row['WEBSITE']);
				   $CUSTOMER_CLASS=mysql_real_escape_string($row['CUSTOMER_CLASS']);   
				    $SLSP_TERR=mysql_real_escape_string($row['SLSP_TERR']); 
					 $TERMS_CODE=mysql_real_escape_string($row['TERMS_CODE']); 
					  $PRICE_LEVEL=mysql_real_escape_string($row['PRICE_LEVEL']);
					   $MTD_SALES_AMT=mysql_real_escape_string($row['MTD_SALES_AMT']);
					    $MTD_COST_AMT=mysql_real_escape_string($row['MTD_COST_AMT']);   
						 $YTD_SALES_AMT=mysql_real_escape_string($row['YTD_SALES_AMT']);
						  $YTD_COST_AMT=mysql_real_escape_string($row['YTD_COST_AMT']); 
						   $PYR_SALES_AMT=mysql_real_escape_string($row['PYR_SALES_AMT']);
						    $PYR_COST_AMT=mysql_real_escape_string($row['PYR_COST_AMT']);
							 $BALANCE=mysql_real_escape_string($row['BALANCE']);
							  $HIGH_BALANCE=mysql_real_escape_string($row['HIGH_BALANCE']);
							   $CREDIT_LIMIT=mysql_real_escape_string($row['CREDIT_LIMIT']);
							    $MESSAGE=mysql_real_escape_string($row['MESSAGE']); 
								 $CUST_PRICE_CLASS=mysql_real_escape_string($row['CUST_PRICE_CLASS']); 
						/*	$ASSIGNED_DATE=$row['ASSIGNED_DATE'];
								   $OPENED_DATE=$row['OPENED_DATE']; 
								    $LAST_SALE_DATE=$row['LAST_SALE_DATE'];
									 $LAST_PMT_DATE=$row['LAST_PMT_DATE'];  
									  $HIGH_BAL_DATE=$row['HIGH_BAL_DATE'];  */
									  
									  




									  
$query3="SELECT * FROM ar_ship_to_addr WHERE CUSTOMER_NUM='$acct'";
echo "$query3<br /><br />";
$result3 = mysql_query($query3);

while($row3 = mysql_fetch_array($result3, MYSQL_ASSOC))
{

$num_rows = mysql_num_rows($result2);
if($num_rows>"0")
{
$acct =$row3['CUSTOMER_NUM'];							               
    $SHIP_TO_NAME=mysql_real_escape_string($row3['SHIP_TO_NAME']);
	   $COMPANY=mysql_real_escape_string($row3['COMPANY']); 
	    $SHIP_TO_ADDR_1=mysql_real_escape_string($row3['SHIP_TO_ADDR_1']);
		 $SHIP_CITY=mysql_real_escape_string($row3['SHIP_CITY']);
		  $SHIP_STATE=mysql_real_escape_string($row3['SHIP_STATE']);  
		   $SHIP_ZIP_CODE=mysql_real_escape_string($row3['SHIP_ZIP_CODE']); 

    
$insert1="INSERT INTO customers VALUES ('0','$COMPANY', '$CUSTOMER_NUM', '$CUSTOMER_NAME', '$ADDRESS_1', '$ADDRESS_2', '$ADDRESS_3', '$CITY', '$STATE', '$ZIP_CODE', '$SHIP_TO_NAME', '$SHIP_TO_ADDR_1', '$SHIP_CITY', '$SHIP_STATE', '$SHIP_ZIP_CODE', '$PHONE_1', '$PHONE_2', '$CONTACT_1', '$CONTACT_2', '$EMAIL_1', '$EMAIL_2', '$WEBSITE', '$CUSTOMER_CLASS', '$SLSP_TERR', '$TERMS_CODE', '$PRICE_LEVEL', '$MTD_SALES_AMT', '$MTD_COST_AMT', '$YTD_SALES_AMT', '$YTD_COST_AMT', '$PYR_SALES_AMT', '$PYR_COST_AMT', '$BALANCE', '$HIGH_BALANCE', '$CREDIT_LIMIT' , '$MESSAGE', '$CUST_PRICE_CLASS', '$TOTAL')";
echo"<br /><br />$insert1<br /><br />";
mysql_query($insert1) or die("MySQL ERROR: ".mysql_error());





}
else
{
$insert2="INSERT INTO customers VALUES ('0','$COMPANY', '$CUSTOMER_NUM', '$CUSTOMER_NAME', '$ADDRESS_1', '$ADDRESS_2', '$ADDRESS_3', '$CITY', '$STATE', '$ZIP_CODE', '0', '0', '0', '0', '0', '$PHONE_1', '$PHONE_2', '$CONTACT_1', '$CONTACT_2', '$EMAIL_1', '$EMAIL_2', '$WEBSITE', '$CUSTOMER_CLASS', '$SLSP_TERR', '$TERMS_CODE', '$PRICE_LEVEL', '$MTD_SALES_AMT', '$MTD_COST_AMT', '$YTD_SALES_AMT', '$YTD_COST_AMT', '$PYR_SALES_AMT', '$PYR_COST_AMT', '$BALANCE', '$HIGH_BALANCE', '$CREDIT_LIMIT' , '$MESSAGE', '$CUST_PRICE_CLASS', '$ASSIGNED_DATE', '$OPENED_DATE', '$LAST_SALE_DATE', '$LAST_PMT_DATE', '$HIGH_BAL_DATE', '$TOTAL')";
echo"<br /><br />$insert2<br /><br />";
mysql_query($insert2) or die("MySQL ERROR: ".mysql_error());	


}
}


  }
    }
  else
  {
  //do nothing
  }

  
  
  
  }

  


?>

[jakebur01]

I found the problem.  This query

SELECT * FROM SA_CUST_ITEM_CLS WHERE ITEM_CLASS = 'YKP' or ITEM_CLASS = 'YKU'or ITEM_CLASS = 'YKT' AND CUSTOMER_NUM='1592'

is returning about 40 rows and it should only be returning about 3.

 

Thant Customer_Num should be preventing this. Do I have something wrong in my query?

 

Jake

[premiso]

Order of operations, you need to use parenthesis.

 

SELECT * FROM SA_CUST_ITEM_CLS WHERE (ITEM_CLASS = 'YKP' or ITEM_CLASS = 'YKU' or ITEM_CLASS = 'YKT') AND CUSTOMER_NUM='1592'

 

The reason is, the customer_num is only associated to the last ITEM_CLASS code, YKT, and it will pull all the other items with YKU / YKP as they are not associated to a customer number. Using the Parenthesis it groups them up. You could also have used the IN() function.

 

SELECT * FROM SA_CUST_ITEM_CLS WHERE ITEM_CLASS IN('YKP', 'YKU', 'YKT') AND CUSTOMER_NUM='1592'