[RESOLVED] Problem with multi table update

minifairy

This routine acts on two tables from a series of multiple tables that comprise ONE MYSQL DB.

Problem is this: The qty from the inventy file is not getting updated for all the quantities in the items file if there are several in a row of the same item. It will end up updating the inventory qty for only the last one.

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 occurrence rather than by inventory id, however, if two of the same item are sold individually in two sequential sales 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);

Hope this makes the problem more clear.

Thanks

vampiricdust

It's because you're using the quantities you got from the initial select statement. You either need to bulk the updates before building and submitting the update query or you need to reacquire the quantity every time to ensure the quantity you have is up to date.

minifairy

Not sure I get this. If it were pulling all the rows from the initial query at the same time the update would not be right if there were two of the same item sold in sales where there were intervening sales that it was not sold. However, in the following senario the update works properly:
sale 1 Item A sold
sale 2 Item B sold
sale 3 Item A sold

If the sales go as follows the update ends up with the wrong quantity in inventory:
sale 1 Item A sold
sale 2 Item A sold
sale 3 Item B sold

So logically the initial query should be pulling the qty from inventory and items as it steps through each row. So logically If I do row 1 for sale one it should update inventory then pull the updated qty for row 2.

What am I missing here???

johanafm

You are missing the fact that you retrieve some values first, such as

inv id, inv qty, item qty
1        10        3
1        10        5
1        10        6

Then, as you go through your result set and update your table, you will set inv qty = (10 - 7), followed by qty= (10 - 5), followed by qty = (10 - 6), but not necessarily in that particular order.

What you need to do is

$result = $db->query('SELECT id FROM item WHERE sold = 0');
# check for query errors

$stmt = $db->prepare('UPDATE inv INNER JOIN item ON inv.id = item.inv_id SET inv.qty = CAST(inv.qty - item.qty AS SIGNED INTEGER), item.sold = 1 WHERE inv.id = :id');
# check for prepare statement errors

while ($row = $result->fetch())
{
	$stmt->execute(array(':id' => $row['id']));
}

And if using mysql, this assumes that you have configured your database to run in strict mode and that inv.qty is UNSIGNED, otherwise values may become negative (if using SIGNED) or may be set to 0 (if not in strict mode mysql lets the query pass with a warning and modifies the value to its end point value).

If you want to use your original approach, you'd have to keep track of how quantitites change over subsequent updates, doing something along the lines of

$quantities = array();

while (...)
{
	# this will only be done once per inventory id
	if (!isset($quantities[$row['inv_id']]))
	{
		$quantities[$row['invid']] = $row->iqty;
	}
	# now replace $nqty with $quantities[$row['invid']] since this is per item and also
	# isn't reset to 0 on each iteration, so just update it if there are enough items left
	if (($new_qty = $quantities[$row['invid']] - $row->qty) >= 0)
	{
		$quantities[$row['invid']] -= $new->qty

	# perform update
}
}

And in particular, please note that

        if($nqty<=0){
            $nqty=0;
        }

most likely isn't what you want. Let's say you have 2 items in stock, and I order 3. 2 - 3 = -1, -1 <= 0, so you happily say that you have 0 items left, rather than -1 item. But you also update my order to sold, and then what? You ship me 2 items instead of 3? You should only update if the quantity left is >= 0, shouldn't you?
Then when you update your inventory, you can perform another check for items not yet marked as sold (and I'd call that shipped instead) and see if you can ship them this time.

minifairy

So it is retrieving the entire set of what is selected rather than row by row. This is what I was afraid of but I guess I don't understand why. But that is another question I suppose.

Thanks.