Can't write negative number to SQL Column

[AzeS]

Dear Community,

I can not make a positive value through subtraction into a negative one.

I have already checked the if query itself and the invoice in the execution, I hope you can help me, greetings AzeS.

private function update_item($ID,$QUA) {

	$sql = $this->db->prepare("SELECT stack,time FROM items WHERE ID=:e");

	$sql->execute(array(':e' => $ID));

	$res = $sql->fetch(PDO::FETCH_ASSOC);

	echo $res['stack'] - $QUA;

	if (count($res)) {

		if ($res['stack'] == 0 || $res['stack'] > 0 || $res['stack'] - $QUA > 0 || $res['stack'] - $QUA == 0  ) {

			echo "HERE1";

			$sql = $this->db->prepare("UPDATE items SET stack=:f AND dod=:g AND position=:h WHERE ID=:e");

			$pa = $res['time'] + 4;

			$pb = $res['time'] + 8;

			$dod = "This item is not there Maybe in. " . $pa . "-" . $pb . " OT's it's there.";

			$sql->execute(array(':f' => $res['stack'] - $QUA, ':g' => $dod, ':h' => 0, ':e' => $ID));

			return 1;		

		} else {

			echo "HERE1";

			$sql = $this->db->prepare("UPDATE items SET stack=:f WHERE ID=:e");

			$sql->execute(array(':f' => $res['stack'] - $QUA, ':e' => $ID));

			return 1;	

		}

	}

	return 0;

}

Edited March 12, 2017 by AzeS

[Barand]

What is the table structure?
 

SHOW CREATE TABLE items;

[AzeS]

dbtest.items.stock015129171.50004.5000ENUM('0','15')

[Barand]

Good luck.

[AzeS]

That will help me out xD 

Edited March 13, 2017 by AzeS

[AzeS]

 `stock` int(5) DEFAULT NULL 
ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=latin1

Better ?

Edited March 13, 2017 by AzeS

[NigelRel3]

Firstly - this line

$sql = $this->db->prepare("UPDATE items SET stack=:f AND dod=:g AND position=:h WHERE ID=:e");

the syntax is incorrect, if you are updating several values - then you use a comma between items - not 'and'.

 

As Barand was asking - what is the table structure?  Can you show the list of columns and what each one is defined as? What you show in your last post show one column and this seems to be different to what your using ('stock' instead of 'stack').

 

Lastly - BUT most importantly - you don't at any point check that your statements have prepared/executed correctly - you simply assume that everything has worked OK and carry on.  You should assume nothing - check at each stage if what you've just done on the database has worked!  At some point this is going to come back and produce more problems.

[AzeS]

private function update_item($ID,$QUA) {
	$sql = $this->db->prepare("SELECT stack,time FROM items WHERE ID=:e");
	$sql->execute(array(':e' => $ID));
	$res = $sql->fetch(PDO::FETCH_ASSOC);
	echo $res['stack'] - $QUA;
	if (count($res)) {
		if ($res['stack'] == 0 || $res['stack'] > 0 || $res['stack'] - $QUA > 0 || $res['stack'] - $QUA == 0  ) {
			echo "HERE1";
			$sql = $this->db->prepare("UPDATE items SET stack=:f, dod=:g, position=:h WHERE ID=:e");
			$pa = $res['time'] + 4;
			$pb = $res['time'] + 8;
			$dod = "This item is not there Maybe in. " . $pa . "-" . $pb . " OT's it's there.";
			$sql->execute(array(':f' => $res['stack'] - $QUA, ':g' => $dod, ':h' => 0, ':e' => $ID));
			return 1;		
		} else {
			echo "HERE1";
			$sql = $this->db->prepare("UPDATE items SET stack=:f WHERE ID=:e");
			$sql->execute(array(':f' => $res['stack'] - $QUA, ':e' => $ID));
			return 1;	
		}
	}
	return 0;
}

Changed that, ill set up an try catch with report as soon as the project is done...

 

 

 

Struct:

 

 

CREATE TABLE `items` (
 `ID` int(255) NOT NULL AUTO_INCREMENT,
 `ESTABLISHED` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `current_owner` int(11) NOT NULL,
 `revid` int(255) NOT NULL,
 `itepiclrg` varchar(255) DEFAULT NULL,
 `itepicsml` varchar(255) DEFAULT NULL,
 `itename` varchar(90) NOT NULL,
 `itevalue0` float NOT NULL,
 `itevalue1` float NOT NULL,
 `detailsstr` text NOT NULL,
 `detailssub` text NOT NULL,
 `weight` int(5) NOT NULL,
 `original` varchar(3) NOT NULL,
 `locationid` int(11) NOT NULL,
 `dod` text,
 `position` int(1) DEFAULT NULL,
 `reward` int(6) DEFAULT NULL,
 `size` varchar(14) DEFAULT NULL,
 `stack` int(5) DEFAULT NULL,
 `time` int(2) DEFAULT NULL,
 `CANCELED` int(1) NOT NULL DEFAULT '0',
 `CNCELED_SINCE` datetime DEFAULT NULL,
 PRIMARY KEY (`PID`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=latin1

Edited March 13, 2017 by AzeS

[NigelRel3]

You still seem to be referring to a field called 'stack' in your SQL, is this the 'stock' field on the table.

[AzeS]

changed that, im sorry, i cant decide between stack and stock 

 

But in genaral it works, but not with the negative value

Edited March 13, 2017 by AzeS

[AzeS]

I going with VARCHAR for now... i let this thread open for thruther suggestions

[Barand]

I going with VARCHAR for now...

Is that your entry for this month's "Most Ridiculous Solution" competition?

 

I have recreated the table from your CREATE TABLE code (except I changed the primary key to a column that actually exists in the table) and tried your function.

 

Negative "stack" values were written without problem.

 

I applaud your attempt at debugging to see which of the two queries is executed but it fails miserably as HERE1 is echoed regardless of which path the logic takes.

 

What is the condition supposed to be (in business terms, forget you're a programmer) for the first query to execute instead of the second? It currently always executes unless there is negative stack value to begin with.

 

When I ran it with a stack value of 5 and QUA value of (leaving 4 ) the "DOD" still said "Item is not there, Maybe in ..."