dividing two columns and set the result in the third PHPMYADMIN?

Question

I am new in PHP world, basically what I want to do is divide two columns and set the result automatically in a third column.

In my table 'Building' I have columns 'Selling' and 'Size'. I want to divide those two to get the Sell price per Sqm and set it in the column 'SqmPrice'.

Like this every time I input the price and the size it calculates it automatically and displays it in my web page.

check here http://stackoverflow.com/questions/6223300/how-to-populate-mysql-column-value-based-on-a-formula — krishna, Mar 04 '14 at 08:01

krishna · Accepted Answer · 2014-03-04T08:28:32.437

3

you have two option

Option 1: create a trigger that set the column to use the formula you assigned. example

delimiter ~

create trigger my_table_insert before insert on tablename
for each row begin
    set new.sqmprice = new.Selling / new.Size; 
end~

delimiter ;

Option2: Use php to calculate the value for formula and insert it

like this

$con=mysqli_connect("localhost","username","password","dbname");
$selling="2";
$size="2";
$SqmPrice = ($selling/$size);

$sql = "insert into tablename(Selling,Size,SqmPrice) values($sellin,$size,$SqmPrice) ";
$query = mysqli_query($sql,$con)or die(mysqli_error($con);

NOTE: Option2 does not update the sqmprice if the selling / size value is updated later

edited Mar 04 '14 at 08:28

answered Mar 04 '14 at 08:12

krishna

4,069
2
29
56

and like this the SqmPrice will be updated automatically eatch time I modify one of the tow field in the webpage ? – Mushroom Mar 04 '14 at 08:18
in that case you have to use option1. create a trigger.Becasue php code formulates only at the place it is used. – krishna Mar 04 '14 at 08:19
or when you modify one of the field update the `sqmprice` programmatically through php code. – krishna Mar 04 '14 at 08:21
I preffer the fisrt option, but I dont know how to do – Mushroom Mar 04 '14 at 08:28
i have added a trigger. use it in phpmyadmin – krishna Mar 04 '14 at 08:33

score 1 · Answer 2 · answered Mar 04 '14 at 08:05

1

UPDATE mytable
SET SqmPrice=Selling/Size

answered Mar 04 '14 at 08:05

Mikpa

1,912
15
20

dividing two columns and set the result in the third PHPMYADMIN?

2 Answers2