Calculation with php and mysql

PHP
1

Hi

I had a problem about to calculate between php and mysql. How I can deduct a value from database in mysql , write a code to integrate php and mysql. I had a form that contain a value that will be deduct from database.

value from database - value from form(php)=$result

the $result will be store in a same table in database.

The conclusion is, how I create a code for calculation?
tq

2

As with most things, there are at least two ways to tackle this problem:

You could retrieve the current value from the database into a PHP variable using a SELECT query, then subtract the value submitted with the form from that PHP variable, and then perform an INSERT or UPDATE query to store the resulting value back into the database.

Alternatively, depending on the database structures involved and the version of MySQL you are running, you may be able to do the whole job using an SQL query. An INSERT or UPDATE query could include a SELECT query that retrieved the existing database value and subtracted the value submitted through the form in order to calculate the new value to be stored in the database. This is a fairly advanced SQL technique, however, and I’d need to know more about your database structure to advise you on what the code would look like.

3

Before writing the code in assumption, can you show us your table structure of source table from where you will get to subtract and target table where you want to store the result so that we can give some codes for you?

4

can’t you just

“UPDATE table SET value = value - “.$form_val.” WHERE id=1”

Edit, ah, two tables??

5

If it is in the same table then it can be something like this i guess:


$sql = "UPDATE table SET result = value - " . mysql_real_escape_string($form_val) . " WHERE id=1";

I just assumed value field is the value from which the form value is to be subtracted and result is the field where you want to store the result.

6

//table stock

CREATE TABLE ‘stock’(
‘stock_id’ int(5) NOT NULL auto_increment,
‘quantity’ int(3) default NULL,
PRIMARY KEY(‘stock_id’)
)

//table take

CREATE TABLE ‘take’(
‘take_id’ int(5) NOT NULL auto_increment,
‘quantity’ int(3) default NULL,
PRIMARY KEY(‘take_id’)
)

//form in insert.php
<form action=“” method=“post” name=“insert”>
<table>
<tr>
<td>Quantity</td>
<td><label>
<input name=“quantity” type=“text” class=“text” id=“quantity” size=“3” maxlength=“3” />
</label></td>
</tr>
<tr>
<td><label>
<input type=“submit” name=“Submit” id=“button” value=“Submit” />
</label></td>
</tr>
</table>
</form>

how to create substraction between php and mysql

this is a formula for subtraction

$result=quantity(from table stock)- quantity(from table take)

7

how to create substraction between php and mysql
this is a formula for subtraction
$result=quantity(from table stock)- quantity(from table take)

You want to subtract quantity of take table with quantity of stock table? Then what is the use of form quantity?

8

form quantity is when I insert the quantity value . that value will be deduct with quantity value in table stock.

quantity value from table stock-quantity value from form quantity

9

$sql = “UPDATE table SET result = value - " . mysql_real_escape_string($form_val) . " WHERE id=1”;

that’s wrong approach.
mysql_real_escape_string means nothing without quotes.
I’d use intval() instead for this case.

revivalx.
In general you do create an SQL query in PHP. That’s the way PHP interacted with mysql. So, as everyone told you, you have to create an SQL query in PHP. And query logic is up to you.