Page 1 of 1
Using math in mysql update statements
Posted: Tue Nov 30, 2010 10:12 pm
by 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");
Re: Using math in mysql update statements
Posted: Tue Nov 30, 2010 10:58 pm
by 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
Re: Using math in mysql update statements
Posted: Fri Dec 03, 2010 10:27 pm
by ConceptDestiny
hallsofvallhalla wrote: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!
Re: Using math in mysql update statements
Posted: Mon Dec 06, 2010 8:17 pm
by 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!
Re: Using math in mysql update statements
Posted: Mon Dec 06, 2010 9:10 pm
by 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...