Help with update query

db::query('update `company_staff` set `money_earned` = `money_earned` + "' . $split[1] . '" where `rank` = 1');
db::query('update `company_staff` set `money_earned` = `money_earned` + "' . $split[2] . '" where `rank` = 2');
db::query('update `company_staff` set `money_earned` = `money_earned` + "' . $split[3] . '" where `rank` = 3');
db::query('update `company_staff` set `money_earned` = `money_earned` + "' . $split[4] . '" where `rank` = 4');

Code (markup):

Heh, that's what I just pulled together. Problem is, those four queries are already inside another loop, so looping once gives 4 queries, looping twice 8, and so on. Is there a way to combine them into one?

 

Find the construct for the case or If statement in you database programmers guide this can be used in the expression in the set part of your statement

From memory it should read something like this

update company_staff set money_earned=money_earned+ case rank=1 then $split[1] else case rank=2 then $split[2] else case rank=3 then $split[3] else case rank=4 then $split[4] else 0 end


You might need to fiddle with end end end end since the case statement is nested but I don't know for certain.

The answer lies in using the case or if statement in the set part of your statement.

 

Thanks chisara, I'll check it out

Edit; Thanks a lot chisara, worked perfectly

 

Always remember that the DB is set oriented, this means use one query to manipulate set(s) of rows.
Depending on your data structure and type of operation it might very well be be possible to transform your loop into the database server itself.
This is very usefull when the loop becomes a performance bottleneck since roundtrips (sequential queries) into the Database take time, eliminate the loop and enhance performance.

Glad to be of assistance.