update table and subtraction[SOLVE]

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
jayson.ph
Forum Contributor
Posts: 165
Joined: Mon Jan 02, 2012 9:20 am
Location: MP
Contact:

update table and subtraction[SOLVE]

Post 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.
Last edited by jayson.ph on Tue Jul 03, 2012 10:56 pm, edited 1 time in total.
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: update table and subtraction

Post 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
?>
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
User avatar
jayson.ph
Forum Contributor
Posts: 165
Joined: Mon Jan 02, 2012 9:20 am
Location: MP
Contact:

Re: update table and subtraction

Post 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
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: update table and subtraction

Post 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
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
User avatar
jayson.ph
Forum Contributor
Posts: 165
Joined: Mon Jan 02, 2012 9:20 am
Location: MP
Contact:

Re: update table and subtraction

Post 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
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: update table and subtraction

Post 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
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
User avatar
jayson.ph
Forum Contributor
Posts: 165
Joined: Mon Jan 02, 2012 9:20 am
Location: MP
Contact:

Re: update table and subtraction

Post 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.
User avatar
jayson.ph
Forum Contributor
Posts: 165
Joined: Mon Jan 02, 2012 9:20 am
Location: MP
Contact:

Re: update table and subtraction

Post 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
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: update table and subtraction

Post 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.
User avatar
jayson.ph
Forum Contributor
Posts: 165
Joined: Mon Jan 02, 2012 9:20 am
Location: MP
Contact:

Re: update table and subtraction

Post 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
User avatar
jayson.ph
Forum Contributor
Posts: 165
Joined: Mon Jan 02, 2012 9:20 am
Location: MP
Contact:

Re: update table and subtraction

Post 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
User avatar
jayson.ph
Forum Contributor
Posts: 165
Joined: Mon Jan 02, 2012 9:20 am
Location: MP
Contact:

Re: update table and subtraction[SOLVE]

Post 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);
Post Reply