Using math in mysql update statements

[ConceptDestiny]

Hey folks,

I was wondering, is there any way to apply a percentage calculation directly into an sql update statement?

ie.

Code: Select all

            $updatecreature="UPDATE animal SET hunger=hunger-10% WHERE owner='$session_username' AND hunger > 100";
            mysql_query($updatecreature) or die("Could not update royal hunger");

[hallsofvallhalla]

your hunger would need to be a float

not sure if you could
$updatecreature="UPDATE animal SET hunger=hunger-(hunger * .10) WHERE owner='$session_username' AND hunger > 100";
mysql_query($updatecreature) or die("Could not update royal hunger");

somehow i doubt it.

You are better querying for the data first but instead of selecting * select hunger only

[ConceptDestiny]

your hunger would need to be a float

not sure if you could
$updatecreature="UPDATE animal SET hunger=hunger-(hunger * .10) WHERE owner='$session_username' AND hunger > 100";
mysql_query($updatecreature) or die("Could not update royal hunger");

somehow i doubt it.

You are better querying for the data first but instead of selecting * select hunger only

The math inside the update line worked perfectly.

I do have another query, which could do with some of that php magic knowledge of yours...

I'm trying to set a variable based off a select * query, then set calculations based on that variable, then proceed to update and set the field value with the new calculated value, but I need it to acknowledge individual values for each entry within the database table and apply the calculation for each, then set the value+calculated value. Any idea why this won't work? Would I need to apply a foreach statement somehow?

Here is a snippet of the code:

Code: Select all

										if ($descript3['gender'] == female);
											{
										       //if age is under adolescent add 1/12th of 40% of the growth left in inches if fed well per month
											$potentiallength = $descript3['potentiallength'];
											$hatchlength = 16;											
											$growthleft = ($potentiallength-$hatchlength);
											$stage1growth = (($growthleft*.40)/12);
													
													if ($descript3['lifestage'] < 3);
														{
															$updateenclosure="UPDATE royals SET length=length+'$stage1growth' WHERE owner='$session_username' AND lifestage < 3 AND hunger < 100 AND health > 49 AND gender = 'female'";
															mysql_query($updateenclosure) or die("Could not update royal length");
														}

I think i've got it arse about face... :/

Thanks for any advice you can offer!

[ConceptDestiny]

Just to reiterate the problem I'm having, I'm not too good with looping update scripts.

I'm trying to update rows of a table, each featuring differing values:

> call rows value's
> use rows value's with a calculation to produce new value
> update original rows value's with new value

Problem is, it's not updating the rows individually. How would I go about applying a loop statement to make the values update to the new value according to their original value? :/

Any help would be greatly appreciated!

[hallsofvallhalla]

i am a little lost on what you are trying to achieve so i will just post a basic sample of what i think you might need.


Create function and have variables to pass into the function that can be interchanged

Code: Select all

function updaterows($var1, $var2, $var3,$var4,$var5,$var6,$var7,$session_username,$gender)
{
 $updateenclosure="UPDATE `$var1` SET `$var2`=`var2`+`$var3`' WHERE `var4`='$session_username' AND 'var5' < 3 AND `var6` < 100 AND `var7` > 49 AND gender = 'female'";
                                             mysql_query($updateenclosure) or die("Could not update royal length");


}

then depending on how you wanna loop and what you are trying to do you can call the function within the loop

Code: Select all

for($i=0;$i<3;$i++)
{
   if ($i == 0)
   {
   updaterows('royal', 'hunger', 'length',$i,$stage,$whatever,$lifestage,$session_username,$gender)
   {
   if ($i == 1)
   {
   updaterows('notroyal', 'thirst', 'butt',$i,$guesswhat,$somethingelse,$lifestage,$session_username,$gender)
   {
 if ($i == 2)
   {
   updaterows('pesant', 'tigers'', 'bears',$lions,$stage,$whatever,$lifestage,$session_username,$gender)
   {
}

ect... ect...