[Skazz]

Hi there!

I hope someone can help me, as I'm driving myself mad trying to figure out what's happening!

I'm a complete newbie at this but I'm trying to fix an inventory system that's malfunctioning. Basically, whenever someone
goes through with a purchase, instead of the amount of stock being taken off the 'instock' amount in the database, it's
updating the database to that amount - for example, starting with 10 items instock, if 3 are purchased, instead of updating
the inventory by -3 (to 7), it's updating the inventory to -3 (thus saying my instock amount is -3), and being totally new
to all this and editing someone elses script, I'm not sure what I need to change!


This appears to be the section of the checkout perl file that is supposed to be updating the inventory:

Code:

### read product data and adjust inventory levels
	for($y=1;$y<=100;$y++){
		$thename="NAME_$y";
			if(length(${$thename})>3){
			$num2++;
			}
		}
	foreach($x=1; $x<=$num2; $x++){
		$qnt="QUANTITY_$x";
		$prce="PRICE_$x";
		$xtnd="XTEND_$x";
		$ide="ID_$x";
		$naam="NAME_$x";
		$adlinfo="ADDTLINFO_$x";
		$shp="SHIPPING_$x";
		${$naam}=~ s/\n//g;

$invent = "SELECT * FROM inventory WHERE itemid='${$ide}'";    

	$sth = $dbh->prepare($invent);
	$sth->execute;
      ($id, $itemid, $description, $instock, $retail, $wholesale, $salePrice, $shipAmount) = $sth->fetchrow_array();
	$serror = ""; $serror = $sth->errstr; if ($serror ne "") {die "SQL Syntax Error: $serror - From: $invent";}

$deduct=${$qnt};
$balance=$instock-$deduct;
$inventupdte = "UPDATE inventory SET instock='$balance' WHERE itemid='${$ide}'";

	$sth = $dbh->prepare($inventupdte);
	$sth->execute;
	$serror = ""; $serror = $sth->errstr; if ($serror ne "") {die "SQL Syntax Error: $serror - From: $inventupdte";}

}

$sth->finish;
$dbh->disconnect;
}

At least I hope this is the right code and that this post is in the correct area of the forum - it's a perl file so I'm
assuming the coding is as well! If it's in the wrong place, please feel free to move it. I also have another post regarding
displaying pound symbols on here, if more than one plea for help at once isn't allowed just let me know and I'll post at a
later date.


Anyways, any and all advice will be very gratefully recieved, I look forward to some help

[UnrealEd]

Try running this query

Code:

UPDATE inventory SET instock-=$deduct WHERE itemid='${$ide}'

instead of

Code:

UPDATE inventory SET instock='$balance' WHERE itemid='${$ide}'

That should work. If, however, it doesn't, you should check the value of the $instock variable. It's probably empty (or 0), and that's causing the wrong value to be inserted into the database

[Skazz]

Wow! Thanks so much for your swift response UnrealEd!

I have checked both the inventory scripts/software and the actual php database and 'instock' is the same number in each - if I update the inventory count, the database also successfully increases, but obviously also unfortunately drops below zero when items are purchased

I implemented the alternate snippet or code you very kindly provided, unfortunately when trying to run the page I get the following error message:

Software error:

SQL Syntax Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-=1 WHERE itemid='S1'' at line 1 - From: UPDATE inventory SET instock-=1 WHERE itemid='S1' at pp-checkout.pl line 478.

(S1 is the demo item I was 'ordering' to test the snippet).
So I'm not quite sure what this means! I don't know my SQL server version but have contacted my hosting company to find out what MySQL server version they are running, and once I know I will post it up here to see if you or any of the good people here can enlighten me about the next step?

Thanks again so much for your help, I'll keep you posted!

*Edited to add: Aha, I found a script to run that would tell me what version is running. It's MySQL version: 5.0.77-log.
The error seems to be suggesting that something in the syntax of the snippet you provided is not compatible with this version?

If so I'd be so grateful to know what needs to be tweaked!

Kind regards and look forward to hearing back again!

[UnrealEd]

Oh, my bad. The query should be like this:

Code:

UPDATE inventory SET instock=instock-$deduct WHERE itemid='{$ide}'

[Skazz]

No problem at all UnrealEd, thanks for your quick response!

Unfortunately now the 'instock' quantity isn't changing at all in either the back end or the database itself!

I don't suppose you have any suggestions?