Page 1 of 1

Need Help with Updating multiple records

Posted: Wed Dec 17, 2008 4:54 pm
by cdoyle
Hi,

I'm having the hardest time with updating multiple records at once.

In this example, I have a table where I want it to reduce the days for each record until it reaches 0. Then once each record reaches 0, it runs some update queries.

I've tried both fetchrow, and also a while.

The example below, it updates each records days_left until the first record reaches 0. Then the other records nothing happens.

If I try using a while instead, then it reduces the days_left 1 for each record in the table. So if there are 5 records, it will reduce each record by 5. So it's going through and running the queries multiple times.

I have to be close, it almost works the way I want it too.

Code: Select all

 
$getcurrent = $db->execute("SELECT e.player_id, e.days_left, e.classes_taken, e.completed, c.strength_update, c.vitality_update, c.agility_update, c.computer_skills_update, c.brain_skill_update, c.job_skill_update, c.class_name from education e
              INNER JOIN classes c ON c.class_id = e.classes_taken
              Where e.completed=0");
$getcurrent1 = $getcurrent->fetchrow();
 
{
 
    if ($getcurrent1['days_left'] <= 1)
    {
        $updatestats = $db->execute("UPDATE `players` SET `strength`=`strength`+?, `vitality`=`vitality`+?, `agility`=`agility`+?, `computer_skills`+?, `P_Job_Skill`+? where `id`=?", array($getcurrent1['strength_update'], $getcurrent1['vitality_update'], $getcurrent1['agility'],$getcurrent1['computer_skills_update'], $getcurrent1['job_skill_update'], $getcurrent1['player_id']));
        
$updatecomplete = $db->execute("UPDATE `education` SET `completed`=?, `days_left`=? WHERE `days_left`<=1", array(1, 0));
 
 
    }
    else
    {
        $reduce = $db->execute("UPDATE `education` SET `days_left`=`days_left`-? WHERE `completed`=0", array(1));
    }
}
 

Re: Need Help with Updating multiple records

Posted: Wed Dec 17, 2008 5:36 pm
by califdon
What is it you're trying to do? Generally it's a bad idea to keep updating records to achieve some kind of timing. The usual strategy is to insert a date (or time), then just READ the records to see if the date or time has arrived that you want to perform some action. Your approach is just asking for mistakes to happen when something goes wrong and the script is run more or less often than once per day!

Re: Need Help with Updating multiple records

Posted: Wed Dec 17, 2008 6:07 pm
by cdoyle
After I posted this, I was wondering that myself.
I'm going to redo this using time instead, and see if that just eliminates my headache all together.

If I still have problems, I'll post back.

Thanks

Re: Need Help with Updating multiple records

Posted: Wed Dec 17, 2008 6:25 pm
by John Cartwright
Indeed. Our company used an opensource software that took this same approach and it was nothing short of a nightmare. For example, if they signed up at 11:59pm and our daily cron would run at 12:00am.. they would not get credit for a full day. That alone was enough to redesign it to datetime fields.