update certain field in mysql for all rows

[MJH Mike]

I need to create a script which will read through all rows of a table and update one certain field in it. The trick is the field needs to be updated using itself myfield+=8 something like that. I know how to run though a table using a do while loop, but while in the middle of this loop i need to do an update to this row. can anyone help me find an efficient way to do this as it will be run by cron every 5 minutes?

[btherl]

Just use this:

[code=php:0]UPDATE table SET field = field + 8[/code]

[MJH Mike]

This is what i came up with, perhaps it will show you what i am trying to accomplish. This works fine but i am sure there is a more efficient way to do this.
[code]<?php
include ('../connect.php');
//<!--update timed stats -->
$query = "SELECT ID, playername, stamina, maxstamina"." FROM km_users";
$result = mysql_query($query) or die('Error, query failed');
        while($row = mysql_fetch_array($result))
{
list($id, $player, $stamina, $maxstamina) = $row;
//calculate percent x/y, where x = amount had, and y = amount possible.
if($stamina<=0)$stamina=0.1;
$stamina+= ceil(0.8/$maxstamina);
if($stamina>$maxstamina)$stamina=$maxstamina;
$updateplayerstats = "Update km_users set stamina='$stamina' where playername='$player'";
      mysql_query($updateplayerstats) or die("Could not update training stats");
}
//<!--end timed stats -->
?>
[/code]

[artacus]

Why? Leave PHP out of it and just update it using MySQL like btherl said.
[code]
UPDATE km_users AS ku
JOIN (
  SELECT ID, IF(stamina <=0,0.1,stamina) AS old_stamina,
  maxstamina, IF(stamina + CEIL(0.8/maxstamina) <= maxstamina,
  stamina + CEIL(0.8/maxstamina) <= maxstamina,
  maxstimina) AS new_stamina
  FROM km_users
) AS sub ON ku.ID = sub.ID
SET ku.stamina = sub.new_stamina[/code]

[btherl]

Given your situation, I might even do it in php, unless efficiency is a problem.

There's pretty much two options - Do the calculation in PHP and do invididual updates, or do the calculation in SQL and do a single update.  One way requires individual updates, the other does a single update.

You can try each and see how it performs.  Good luck :)