[SOLVED] calculations within a field

[bhavin_85]

hey guys

 

i have a field which is float, it stored the price of an item, i need to create another field called points which times the price by 0.1 and stores that value.

 

how would i do this? can the sql database do it itself or would i need to create a function in php that will convert it then store the points?

 

cheers

 

[trq]

UPDATE tbl SET points = otherfield * 0.1

 

May work.

[Snooble]

if not use the UPDATE sql and just do the calc's in php:

 

$text1 = (echo price of item)
$text2 = $text1 * 0.1

UPDATE tablename SET pointsfieldname = $text2

 

[bhavin_85]

ok so i guess from what ur saying ill have to write the code in my form which enters the info into that database, ill have a go at it

 

[obsidian]

ok so i guess from what ur saying ill have to write the code in my form which enters the info into that database, ill have a go at it

 

What thorpe is suggesting is to let your SQL do it. I agree with him because you would simply have to write a single query to update all the fields. Once you add your new field, just run the query he posted above, and it will update the whole table for you.

[bhavin_85]

that worked

 

i just tried it in myphpadmin, how do i get the table to run that query everytime its updated? in myphpadmin? sorry thats probably a total n00b question  ???

[Snooble]

You can run mysql from .php pages.

 

Just set the query into a variable like so:

 

$sql = "PUT QUERY HERE (e.g. SELECT BLAH FROM BLAH/UPDATE diggerydoo)";
mysql_query($sql) or die ("Couldn't execute $sql: " . mysql_error()); 

 

put that in your page and you'll see, replace "PUT QUERY HERE" with the query (rolls eyes)

 

Snooble

[bhavin_85]

i knewu could run them from php pages, thought there might be a way to make the sql database exectute the query automatically everytime a new row is added

 

cheers

guys

[trq]

thought there might be a way to make the sql database exectute the query automatically everytime a new row is added

 

Depending on your version of mysql you may be able to use triggers. Im not 100% up on there syntax but check the mysql manual.