Jump to content

adding fields

jakebur01

By jakebur01
in PHP Coding Help

 Share

Recommended Posts

jakebur01 Advanced Member

jakebur01

Posted
Posted

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

Link to comment
Share on other sites
jakebur01 Advanced Member

jakebur01

Posted
  • Author
Posted

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

Link to comment
Share on other sites
jakebur01 Advanced Member

jakebur01

Posted
  • Author
Posted

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
  }

  
  
  
  }

  


?>

Link to comment
Share on other sites
jakebur01 Advanced Member

jakebur01

Posted
  • Author
Posted

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

Link to comment
Share on other sites
premiso Regular Member

premiso

Posted
Posted

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'

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.