PHP/MySql update query help

[cdoyle]

Hi,

 

I'm trying to create an update query that runs via a cron job.

 

This is a mod I'm making for ezRPG, I've created a new table called 'Jobs'.

It's fields are

 

Job_ID

Pay

Job_Skills

 

I've added a field to my players table called 'Players_Job_ID'

So when a player applies for a job, this field is updated with the Job_ID from the Jobs table for whichever job they applied for.

 

So now back to cron,  I need to run an update query that gives the players their salary each night.

I need to run a query that updates the 'gold' field for any player that might have a job and pays them according to which job they have.  This is the amount listed in the 'Pay' field of the Jobs table.

 

At first I tried this

$query_all=$db->execute("select * from `players`");
while($reset = $query_all->fetchrow())
{
$interest_rate = intval($reset['bank'] * 0.03);
$query = $db->execute("update `players` set `bank`=? where `id`=?", array($reset['bank'] + $interest_rate, $reset['id']));
$interest_rate = intval($reset['CAC_First_International'] * 0.05);
$query = $db->execute("update `players` set `CAC_First_International`=? where `id`=?", array($reset['CAC_First_International'] + $interest_rate, $reset['id']));
}


$query_jobs=$db->execute("select * from `Jobs` where players->Players_Job_ID = Job_ID");
while($jobs = $query_jobs->fetchrow())
{
$query = $db->execute("update `players` set `gold`=? where `id`=?", array(players->gold + $jobs['Pay'], $reset['id'));

}
?>

 

Jump down to $query_jobs

 

Should I have a where clause there? 

All players could have different jobs or no job at all, it has to match the job IDs from the players and job tables and then use the 'Pay' for that job and add it to the field 'gold' for each player.

 

 

 

 

[sasa]

pull all data with one query

 

$query_all=$db->execute("select players.*, Jobs.Pay from `players` LEFT JOIN Jobs ON players.Players_Job_ID=Jobs.Job_ID");

[cdoyle]

Thanks!

 

I just gave it a try, and I'm still getting this error

syntax error, unexpected T_OBJECT_OPERATOR, expecting ')' in <b>/home/caraudi/cron/reset.php</b> on line <b>29</b><br />

 

 

Where does it think I need the )

 

Here is my current code

$query_all=$db->execute("select * from `players`");
while($reset = $query_all->fetchrow())
{
$interest_rate = intval($reset['bank'] * 0.03);
$query = $db->execute("update `players` set `bank`=? where `id`=?", array($reset['bank'] + $interest_rate, $reset['id']));
$interest_rate = intval($reset['CAC_First_International'] * 0.05);
$query = $db->execute("update `players` set `CAC_First_International`=? where `id`=?", array($reset['CAC_First_International'] + $interest_rate, $reset['id']));
}
$query_all=$db->execute("select players.*, Jobs.Pay from `players` LEFT JOIN Jobs ON players.Players_Job_ID=Jobs.Job_ID");
while($jobs = $query_all->fetchrow())
{
$query = $db->execute("update `players` set `gold`=? where `id`=?", array(players->gold + $jobs['Pay'], $reset['id')));

}
?>

[zenag]

just at the last line.....$query = $db->execute("update `players` set `gold`=? where `id`=?", array(players->gold + $jobs['Pay'], $reset['id']));

[zenag]

hai..try to rectify simple faults by urself....that makes u proficiency....

[cdoyle]

I noticed I had a copy/paste error on this line

$query = $db->execute("update `players` set `gold`=? where `id`=?", array(players->gold + $jobs['Pay'], $reset['id']));

 

that's how I have it on my site and get the error.

 

I'm trying a search for that error on the forum, and only this post comes up.

 

 

[cdoyle]

I've updated the code to this,

and it removes the error I was getting.  But it's not updating the gold field.

 

Does this look like it's right?

 

$query = $db->execute("update `players` set `gold`=? where `id`=?", array($reset['gold'] + $jobs['Pay'], $reset['id']));