Fairly simple issue

[jaydee]

Hey,

 

I'm sure this is fairly simple but I've been coding hard every day for the past week that I've reached that point where even remembering what global variables I've set is a problem!

 

Right. I have 2 tables this concerns, the `users` one and the `positions` one. Basically users buy the positions and so the link between them that is in the 'positions' table it has the username of it's owner.

 

Where I come upon a problem is the positions earn money (affiliation) and this is done manually at the end of each day. BOTH need to be updated but what happens is positions are selected on certain criteria and only those ones are paid. So what is the best way to update both with the new information (position table just positionearnings+$addedtoday and users table just balance+$addedtoday)? The positions one is easy but the only way I can see really is to have a while loop that would grab the username for that position, then update the users table 1 at a time. But there are at least 1000 positions that are paid every day so that would mean 1000 statements. I'm happy to go with this if there won't be any problems since it's just once per 24 hours but I'm SURE there's a easier way like creating a while loop that just adds a 'OR' trigger to a mysql statement so all users affected are updated in a single statement.

 

Thanks in advance for any help!

 

If you need more info let me know.

[requinix]

I believe you can update both tables at once:

UPDATE users u JOIN positions p ON u.username = p.username
SET p.positionearnings = p.positionearnings + $M, u.balance = u.balance + $N
WHERE whatever conditions you want

Gotta love MySQL's flexibility.

[jaydee]

Thanks a lot for that! Worked great!