[RESOLVED] Problem with multifile update

minifairy

I have a program that updates multiple files. Basically it is an inventory update that updates the quantity and number sold based on the number sold in another file.

The update works fine unless the same item is sold in two sequential sales, in which case it does not use the updated qty on the second sale.

So as it steps through the while loop it is supposed to select the qty(A) from inventory and the qty from the sale line item where the inventory item number and the sale inventory item number are the same.

Then it calculates the new inventory qty by subtracting the sale qty.

Next it updates inventory changing the qty to the calculated qty and marking the sale as updated.(😎

Now next iteration through the while statement. If the same inventory item is sold it uses the qty (A) above rather than the updated qty which was supposed to have been saved in (😎 If the item numbers are different it works properly.

I thought maybe some variables were carrying over so I initialized them before calculating in each iteration. But the value coming in in the $row is not updating.

Why? And How do I prevent it?

dagon

if ever there was a need for a db over flat-file, this is it

scop1983

Parse error: syntax error, unexpected $end in /home/content/11/7412711/html/response.php on line 115..anyone with a clue
Thanks

minifairy

Not sure I understand your response Dragon.

mysql is a db and this is a multi file db that I am working with.

bradgrafelman

Not sure I understand your response, minifairy.

A DBMS is used to store and manipulate sets of data. A single MySQL server can house several different databases consisting of several different tables each.

The fact that your flat-file DB structure spans multiple files has nothing to do with the fact that using a DBMS such as MySQL would probably be a lot easier, more efficient, scalable, etc. etc.

minifairy

But I AM USING MYSQL This is why I don't understand the response. This is a series of multiple tables/files that comprise ONE MYSQL DB.

I am using a multi-file query and updating as I step through the row. I reduced the probability of the problem by ordering the sale items file by occurance rather than by inventory id, however, if two of the same item are sold individually in two sequential sale it will still occur.

Here is the code.

$page_title= "Sales - Update";
	$query="select itemid,invid, updt,items.qty,item,inventy.qty as iqty,sld from items, inventy where items.updt != 'Y' and items.invid=inventy.item order by items.itemid";
	$result = mysql_query($query);
    while($row = mysql_fetch_object($result)){
		$nqty=0;
		$nsld=0;
		$iqty=$row->iqty;
		#echo "Previously Sold: ".$row->sld."  This Sale ".$row->qty."<br>";
		$nsld=$row->sld+$row->qty;
		$nqty=$iqty-$row->qty;
		if($nqty<=0){
			$nqty=0;
		}
		echo "Previous Qty: ".$iqty."  New Qty ".$nqty."<br>";
		$iid=$row->itemid;
		$ivid=$row->item;
		$query2="update items, inventy set items.updt='Y', inventy.qty='$nqty', inventy.sld='$nsld' where items.itemid='$iid' and inventy.item='$ivid'";
		$result2=@mysql_query($query2);
	}
	$query3="update sales set updt='Y' where updt !='Y'";
	$result3=@mysql_query($query3);

dagon

I have a program that updates multiple files.

No one would think DB when you say that.

minifairy

Yea I guess maybe I should have posted in the MySQl threads.