Cron not quite working

[Nullifi3d]

PHP Code:

$armysys = $vbulletin->db->query_read("SELECT strike_action, defense_action, spy_rating, sentry_rating, attack_soldiers, defense_soldiers, untrained_soldiers, spies, sentries FROM " . TABLE_PREFIX . "armysys");
while ($armysys = $vbulletin->db->fetch_array($armysys)) {
    $rank = ( ($armyinfo['strike_action'] + $armyinfo['defense_action'] + $armyinfo['spy_rating'] + $armyinfo['sentry_rating']) / (4) );
    $gold = ( ($armyinfo['attack_soldiers'] * 15) + ($armyinfo['defense_soldiers'] * 15) + ($armyinfo['untrained_soldiers'] * 5) + ($armyinfo['spies'] * 10) + ($armyinfo['sentries'] * 10) );
    $vbulletin->db->query_write("UPDATE " . TABLE_PREFIX . "armysys SET rank = '$rank', gold = gold + '$gold'");
} 


I have tried so many variations of code that my brain hurts. I need this code to update the field rank in armysys with $rank (the average of the 4 fields you see in the variable). As well it neess to update the field gold with $gold (the sum of the fields in the variable). Depending on what I change it sometimes doesn't do anything, but it usually just updates ever row witht he same info.

[Marco van Herwaarden]

PHP Code:

$armysys = $vbulletin->db->query_read("SELECT strike_action, defense_action, spy_rating, sentry_rating, attack_soldiers, defense_soldiers, untrained_soldiers, spies, sentries FROM " . TABLE_PREFIX . "armysys"); 


Here you assign a SQL-Resource to $armysys.

PHP Code:

while ($armysys = $vbulletin->db->fetch_array($armysys)) { 


And here you overwrite the same variable with the data of the first row retrieved.

[Nullifi3d]

PHP Code:

$armysys = $vbulletin->db->query_read("SELECT strike_action, defense_action, spy_rating, sentry_rating, attack_soldiers, defense_soldiers, untrained_soldiers, spies, sentries FROM " . TABLE_PREFIX . "armysys"); 


If I use Query_first it doesn't work.

Sorry, it's suppose to be:

PHP Code:

while ($armyinfo = $vbulletin->db->fetch_array($armysys)) { 


I use this code elsewhere and it works fine:

PHP Code:

$links = $db->query_read("SELECT pl_href AS href, pl_title AS title, pl_anchor AS anchor FROM " . TABLE_PREFIX . "user WHERE pl_href != ''");
while ($link = $db->fetch_array($links)) eval('$premium_links .= "' . fetch_template('directory_premium_links') . '";'); 


This works fine to print out each row that $links stores. Why doesn't this query (whether or not I use query_read or query_first):

PHP Code:

 $armysys = $vbulletin->db->query_read("SELECT strike_action, defense_action, spy_rating, sentry_rating, attack_soldiers, defense_soldiers, untrained_soldiers, spies, sentries FROM " . TABLE_PREFIX . "armysys");
while ($armyinfo = $vbulletin->db->fetch_array($armysys)) {
    $rank = ( ($armyinfo['strike_action'] + $armyinfo['defense_action'] + $armyinfo['spy_rating'] + $armyinfo['sentry_rating']) / (4) );
    $gold = ( ($armyinfo['attack_soldiers'] * 15) + ($armyinfo['defense_soldiers'] * 15) + ($armyinfo['untrained_soldiers'] * 5) + ($armyinfo['spies'] * 10) + ($armyinfo['sentries'] * 10) );
    $vbulletin->db->query_write("UPDATE " . TABLE_PREFIX . "armysys SET rank = '$rank', gold = gold + '$gold'");
} 


[Marco van Herwaarden]

Well with $armyinfo on the second line it looks already much better to me.

[Nullifi3d]

well

PHP Code:

$armysys = $vbulletin->db->query_first("SELECT strike_action, defense_action, spy_rating, sentry_rating, attack_soldiers, defense_soldiers, untrained_soldiers, spies, sentries FROM " . TABLE_PREFIX . "armysys");
while ($armyinfo = $vbulletin->db->fetch_array($armysys)) {
    $rank = ( ($armyinfo['strike_action'] + $armyinfo['defense_action'] + $armyinfo['spy_rating'] + $armyinfo['sentry_rating']) / (4) );
    $gold = ( ($armyinfo['attack_soldiers'] * 15) + ($armyinfo['defense_soldiers'] * 15) + ($armyinfo['untrained_soldiers'] * 5) + ($armyinfo['spies'] * 10) + ($armyinfo['sentries'] * 10) );
    $vbulletin->db->query_write("UPDATE " . TABLE_PREFIX . "armysys SET rank = '$rank', gold = gold + '$gold'");
} 


this does nothing
and

PHP Code:

$armysys = $vbulletin->db->query_read("SELECT strike_action, defense_action, spy_rating, sentry_rating, attack_soldiers, defense_soldiers, untrained_soldiers, spies, sentries FROM " . TABLE_PREFIX . "armysys");
while ($armyinfo = $vbulletin->db->fetch_array($armysys)) {
    $rank = ( ($armyinfo['strike_action'] + $armyinfo['defense_action'] + $armyinfo['spy_rating'] + $armyinfo['sentry_rating']) / (4) );
    $gold = ( ($armyinfo['attack_soldiers'] * 15) + ($armyinfo['defense_soldiers'] * 15) + ($armyinfo['untrained_soldiers'] * 5) + ($armyinfo['spies'] * 10) + ($armyinfo['sentries'] * 10) );
    $vbulletin->db->query_write("UPDATE " . TABLE_PREFIX . "armysys SET rank = '$rank', gold = gold + '$gold'");
} 


this sets all ranks in all rows to 0 and totals up the gold for all rows and adds it to every row. At this time there are only 2 rows in the table.

[Marco van Herwaarden]

Well right now you are looping all rows in 'armysys'. Then for each row in that table, you are setting the values for all rows.

You should add a WHERE-clause to your UPDATE-statement, uniquely identifying the row you're working on.

[Nullifi3d]

I need to update every row in the table indepedantly, not just some of them. This is why i don't have a where clause. Every hour the cron is suppose to give every member that is signed up (row in armysys) gold and also suppose to update their rank.

[Marco van Herwaarden]

What is happening with your current script is:
1. A row (member) is read from armysys
2. With the information of this row, you do some calculations
3. ALL ROWS (members) are updated with the outcome of teh calculation performed for 1 member.
4. goto -> 1.

You will need to use the memberid of the current row (for which you've done calculations) in the update WHERE-clause.

The way you have it now, all members will get the outcome of the calculation of the last member when this script is finished.

[Nullifi3d]

Sorry for my ignorance. I finally got it to work with:

PHP Code:

$armysys = $vbulletin->db->query_read("SELECT userid, strike_action, defense_action, spy_rating, sentry_rating, attack_soldiers, defense_soldiers, untrained_soldiers, spies, sentries FROM " . TABLE_PREFIX . "armysys");
while ($armyinfo = $vbulletin->db->fetch_array($armysys)) {
    $rank = ( ($armyinfo['strike_action'] + $armyinfo['defense_action'] + $armyinfo['spy_rating'] + $armyinfo['sentry_rating']) / (4) );
    $gold = ( ($armyinfo['attack_soldiers'] * 15) + ($armyinfo['defense_soldiers'] * 15) + ($armyinfo['untrained_soldiers'] * 5) + ($armyinfo['spies'] * 10) + ($armyinfo['sentries'] * 10) );
    $vbulletin->db->query_write("UPDATE " . TABLE_PREFIX . "armysys SET rank = '$rank', gold = gold + '$gold' WHERE userid = " . $armyinfo['userid']);
} 


I do have one other question though. This cron runs once every hour (through vbulletin) and I need to display only the minutes this specific cron has left before it runs again. How would I do this?