[SOLVED] Weird PHP/MySQL loop, while error thing.

[scott.stephan]

A descriptive subject, right? Anyway, here's my problem. I recieve a file like this:

 

A

B

B

A

B

B

B

 

Where A is customer info and B is items ordered. There's always an indeterminate # of B rows. My output file has to look like this

AB

AB

AB

AB etc etc.

 

So, no problem! I got this to happen! Then our warehouse started asking for a lot of little changes. This is happening in the middle of 4897894784 other things and it's a rush job, so my code got a little convoluted on teh output end. My problem now is that in the "A" line there's info like "New Jersey". It needs to be "NJ". So, I created a table in the SQL DB called "states" with two fields (Stolen from: http://www.inkplant.com/code/us-state-mysql-table.php), "name" and "abv", ie. "New Jersey, NJ".

 

Basically, now I'm saying "If the current A row state == any state in "states", set the A row state EQUAL to the abv of states". And this works! Once!

 

My problem is that this only works for the first row in the "A" table. Then it conks. I'm sure I'm overlooking something, but I've been staring at it for about an hour and I can't quite get my head around it and I have 10 other things knocking at my door, so: Code and Output, any help is great.

 

//OUTPUT the file correctly
$list = array(); //Holds the final A+B lines
$alines= array();
$counter=1; // For place in array. A1, A2 etc etc.
$curr_id=0;
$b_id=0;
//Open an A file connection
$queryall="SELECT * FROM planz_customers";
$result=mysql_query($queryall) or die(mysql_error());
//Open a connection to the STATES table for Full > Abbreviation conversion. 
$querystate="SELECT * FROM states";
$resultstate=mysql_query($querystate) or die(mysql_error());
//Gets all of the A Lines, drops them into an array with position equal to their temp_id	
while($row=mysql_fetch_array($result)){
	//Warehouse can't handle the RAW POWER of alphanumerics, so we trim the alphas. Thank jeebus they're predictable valeus- Ordernum is "SO", so the first num, CusNum starts with "D", so we lob the 1st. We trim Category down to 1 char.
	//Country is hardcoded as "USA"- If this ever needs to change, you'll have to pop in the country codes as per Tim's initial document
	$ordernum=substr($row[order_num],2);
	$cusnum=substr($row[sold_to_num],1);
	$billstate=$row[bill_State];
	$shipstate=$row[ship_State]; 
	echo "BILL STATE $billstate <br/>";
	echo "Ship STATE $shipstate <br/>";
		//GET DAT STATE CODE.
		[b]while($staterow=mysql_fetch_array($resultstate)){
			echo "IN STATE ROW <br/>";
			if($billstate == $staterow[name]){
				$billstate=$staterow[abv];
			}
			if($shipstate == $staterow[name]){
				$shipstate=$staterow[abv];
			}
		}[/b]
	$a_line=$ordernum.",".$row[time_create].","."CON".",".$row[priority].","." ".","." ".",".$row[carrier].","." ".",".$cusnum.",".$row[bill_Cus_Name].",".$row[bill_Add_1].",".$row[bill_Add_2].",".$row[bill_City].",".$billstate.",".$row[bill_Zip].","."USA".",".$row[bill_Attn].",".$row[ship_Name].",".$row[ship_Add_1].",".$row[ship_Add_2].",".$row[ship_City].",".$shipstate.",".$row[ship_Zip].","."USA".",".$row[ship_Attn].","." ".",";
	echo "A LINE: $a_line <br /> <br/> \n \n";
	$curr_id=$row[temp_id];
	$alines[$curr_id]=$a_line;
} //End A

 

The output:

 

BILL STATE New York

Ship STATE New York

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

IN STATE ROW

A LINE: 100039,06/19/2009,CON,5, , ,SWU11, ,100037,Test Dude,1765 Broadway Suite 716,,New York,NY,10019,USA,Test Dude,Lee,1765 Broadway Suite 716,,New York,NY,10019,USA,Test Dude, ,

 

BILL STATE New Jersey

Ship STATE New York

A LINE: 100041,06/19/2009,CON,5, , ,SWU21, ,100039,JuneSevenTeen TestLast,123 Testing Billing Road,,Bloomfield,New Jersey,07003,USA,JuneSevenTeen TestLast,JuneSevenTeenShipping,567 Shipping Test Lane,,Brooklyn,New York,11206,USA,JuneSevenTeenShipping JuneSevenTeenShipL, ,

 

B LINE: 17010001,Smooth Operator 16oz,1

 

FULL LINE: 100039,06/19/2009,CON,5, , ,SWU11, ,100037,Test Dude,1765 Broadway Suite 716,,New York,NY,10019,USA,Test Dude,Lee,1765 Broadway Suite 716,,New York,NY,10019,USA,Test Dude, ,17010001,Smooth Operator 16oz,1

 

B LINE: 17010001,Smooth Operator 16oz,3

 

FULL LINE: 100041,06/19/2009,CON,5, , ,SWU21, ,100039,JuneSevenTeen TestLast,123 Testing Billing Road,,Bloomfield,New Jersey,07003,USA,JuneSevenTeen TestLast,JuneSevenTeenShipping,567 Shipping Test Lane,,Brooklyn,New York,11206,USA,JuneSevenTeenShipping JuneSevenTeenShipL, ,17010001,Smooth Operator 16oz,3

 

 

It only runs that STATE ROW stuff for the first record. I'm seeing the problem, just not the solution. Thoughts?

[scott.stephan]

Duh. I swear, I post here and 20 seconds later I figure it out.

 

Had to run it inside the loop, otherwise by the time the first record had run, $staterow would never = $resultstate again.