Jump to content
MakeWebGames

Recommended Posts

Posted

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

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...