php mysql update via cron

Question

I have a php file that I run via cron. For testing I've set it to run every hour, but normally it will run once a day. What I want is to make it so that if the player has the autotechrefinery field set to 1, then they should have their population pulled from the table, how much money, research supplies, and technology they have.

From there, what I want is to deduct 500 researchsupplies, add 50 technology, and deduct 10% of population from money. All of this seems to be working when I run the file manually, but when I let it run automatically via cron, there is something that isn't working that as a result it sets technology to 50, researchsupplies to -500 and money to 0.

$refCheck = mysql_query("SELECT * FROM players WHERE autotechrefinery='1'");
while($rC = mysql_fetch_array($refCheck)) {

$nation = $rC['nation'];
$pop = $rC['population'];
$rsupplies = $rC['researchsupplies'];
$cash = $rC['money'];
$tech = $rC['technology'];

$newtech = $tech+50;
$newmoney = $cash-($pop*.1);
$newsupplies = $rsupplies-500;

mysql_query("UPDATE players SET money='$newmoney', technology='$newtech', researchsupplies='$newsupplies', techbought='1' WHERE autotechrefinery='1'"); }

at the end of the loop you are updating every row with `autotechrefinery` set to 1, so every record will have values calculated from last one fetched from db. Are you sure it is working as expected? — dev-null-dweller, Dec 22 '12 at 12:06
You could add as well the entry in crontab and the string you execute manually. Aside from that this method of updating does not look safe: another script may update database records and your subsequent update would overwrite the values. UPDATE players SET money=money-(population * 0.1) etc would do the trick. Though you may have some logic that is hard to embbed into SQL statement — Max Yakimets, Dec 22 '12 at 12:07
You are using [an **obsolete** database API](http://stackoverflow.com/q/12859942/19068) and should use a [modern replacement](http://php.net/manual/en/mysqlinfo.api.choosing.php). — Quentin, Dec 22 '12 at 12:13

score 2 · Accepted Answer · answered Dec 22 '12 at 12:12

The bug in your code is: the UPDATE will always update all entries. So all entries get the value assigned of the last pass of your loop.

To fix the code you need some ID to identify the row and use it in the update:

$refCheck = mysql_query("SELECT * FROM players WHERE autotechrefinery='1'");
while($rC = mysql_fetch_array($refCheck)) {   
  $ID = $rC['id'];
  $nation = $rC['nation'];
  $pop = $rC['population'];
  $rsupplies = $rC['researchsupplies'];
  $cash = $rC['money'];
  $tech = $rC['technology'];

  $newtech = $tech+50;
  $newmoney = $cash-($pop*.1);
  $newsupplies = $rsupplies-500;

  mysql_query("UPDATE players SET money='$newmoney', technology='$newtech', researchsupplies='$newsupplies', techbought='1' WHERE id="$ID');
}

You didn't ask for improvements but you could achieve the same result with one SQL statement:

UPDATE players set money=money - population * .1, technology = technology + 50, researchsupplies = researchsupplies - 500;

We have an ID it just didn't click we needed to use that. And thank you for that much cleaner looking UPDATE. Thanks so much! — Greene345, Dec 22 '12 at 12:19

php mysql update via cron

1 Answers1