Page 1 of 1

update table and subtraction[SOLVE]

Posted: Tue Jul 03, 2012 1:10 am
by jayson.ph
Hi all.

product_balance = 10
sold_product = 1

how to update table when we are using minus/subtraction? ex. i put or i add 5 in sold_product and now the product_balance now is 5. how could i do this in the correct syntax.

any guide or basic sample please..

Thank.

Re: update table and subtraction

Posted: Tue Jul 03, 2012 1:26 am
by social_experiment
You could do the subtraction prior to updating the table (using PHP);

Code: Select all

<?php
 $product_balance = 10;
 $sold_product = 1;

 // 
 $new_product_balance = $product_balance - $sold_product;
 // insert $new_product_balance into the database
?>

Re: update table and subtraction

Posted: Tue Jul 03, 2012 1:39 am
by jayson.ph
social_experiment" do you have any other sample, since when i input 5 in sold_product the previous value of sold_product 1 will also change into 5

Re: update table and subtraction

Posted: Tue Jul 03, 2012 2:33 am
by social_experiment
Could you give a bit more background info as to how the script currently works, how the amount of sold products are entered, etc

Re: update table and subtraction

Posted: Tue Jul 03, 2012 2:56 am
by jayson.ph
okay, here: but it is not work.

Code: Select all

$sql = "UPDATE tbl_recieve SET return_on, balancs = $_POST[balancs] - $_POST[return_on] WHERE id = '$varid'"; 
$result = mysql_query($sql);
Image

Re: update table and subtraction

Posted: Tue Jul 03, 2012 4:15 am
by social_experiment

Code: Select all

<?php
 $balance = $_POST['balancs'];
 $return_on = $_POST['return_on'];

 $remainder = $balance - $return_on;

 $sql = UPDATE tbl_receive SET balance = '" . $remainder . "' WHERE id = '" . $varid . "' ";
 $result = mysql_query($sql);

?>
This is how i would do it; remember to do checks on the values you receive and escape input; this snippet above is vulnerable to injection.

A note about updating multiple values in SQL

Code: Select all

UPDATE
table_name
SET
field1 = value1,
field2 = value2
The php code will be

Code: Select all

<?php
 $sql = "UPDATE table SET field1 = '$value1', field2 = '$value2' WHERE id = '$id'";
?>
hth

Re: update table and subtraction

Posted: Tue Jul 03, 2012 4:47 am
by jayson.ph
ah akay, [social_experiment] thank you for this and last 1question,

ex. after we input we have now a value like this below.
ealier:
sold = field1 = 00
balance = field2 = 100

Now:
sold = field1 = 50
balance = field2= 50

how about if the user he/she update again in same field but the field value are now change after we input. ex. now we sold more than 50, let say 60. so when i update the table, into 60 it is lessthan the value into balance = field2= 50. so what should it be possible, to do instead the value now of field2 is 40. since the manager he want to show the total sold as well.

Re: update table and subtraction

Posted: Tue Jul 03, 2012 5:06 am
by jayson.ph
[text]This is how i would do it; remember to do checks on the values you receive and escape input; this snippet above is vulnerable to injection.

A note about updating multiple values in SQL[/text]

Thank You

Re: update table and subtraction

Posted: Tue Jul 03, 2012 5:44 am
by mikosiko
a good DB modeling technic is never store dependent (calculated) column values in the table, doing so violate DB normalization rules.

calculated values should/can be determined when you retrieve the rows.

Re: update table and subtraction

Posted: Tue Jul 03, 2012 7:26 pm
by jayson.ph
ah Okay, thanks for that info. and now i change it/ and normalize for an easy way, for the user.. and i give you the feedback after this, if i success.. thanks

Re: update table and subtraction

Posted: Tue Jul 03, 2012 9:41 pm
by jayson.ph
Hi all,

please tell what should better to this situation, i have [return] [sold] [balance]. base on image below, earlier the balance is 40. but after i input 2 in return, the balance now is 38. and now i add 1 for return witch is return comes into 3 and the balance is 37. and how about in sold it is also deduct into balance, ex. i input 3 in sold base on image.the total balance now is 34. the hardest things to do this part for me is. together 2 fields deducted into 1 field witch is balnce.

Please help me, what sql statement shuld i use.

Image

Re: update table and subtraction[SOLVE]

Posted: Tue Jul 03, 2012 10:55 pm
by jayson.ph
Problem solve guys.. here the source.

Code: Select all

$varpingbalancee = $varvalreturn + $varvalsold;
			$varpingbalanceee = $varBalance - $varpingbalancee;
			$varpingreturn = $varreturn_on + $varvalreturn;	
			$varpingsold = $varvalsold + $varsold;
			
				$pingsql = "UPDATE tbl_recieve SET balancs = '$varpingbalanceee', return_on = '$varpingreturn', sold = '$varpingsold' WHERE id = '$varid'";
				$pingresult = mysql_query($pingsql);