Case

[Decepti0n]

Just something I learned today which might come in handy

I had to cut down this set of queries:

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');

 

Which was obviously pretty inefficient, especially when it was inside a loop. Asked for help, and this is where Mysql's case comes in

 

CASE case_value

WHEN when_value THEN statement_list

[WHEN when_value THEN statement_list] ...

[ELSE statement_list]

END CASE

Long story short, I ended up with this:

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

 

i.e. 4 queries down to one 8-)

[Haunted Dawg]

Re: Case

This is very handy, can come in handy to make mccode's more efficient.