Page 1 of 1
MySQL IF statement/function
Posted: Tue Feb 10, 2009 6:44 am
by batfastad
Hi everyone
I've just discovered that MySQL has an IF function

It could be very useful to do some simple conditional numeric calculations in MySQL. Especially where a particular report outputs in several different formats, could cut 50 lines of PHP out of each report
Just wondering though, is it advised to use it?
Or is it always best to handle that sort of thing in the programming language (PHP)?
A simple example of what I currently do in PHP is:
Code: Select all
if ($currency == 'EUR') {
$total_gbp = $total / $exchange_rate_eur;
} elseif ($currency == 'USD') {
$total_gbp = $total / $exchange_rate_usd;
} else {
$total_gbp = $total;
}
And various other simple things. Would be cool to just do that in MySQL using IF, but I don't want to kill performance.
Thanks, B
Re: MySQL IF statement/function
Posted: Tue Feb 10, 2009 8:19 am
by josh
You're supposed to encapsulate that kind of stuff in objects. Putting business logic in mysql is not advised. Stored procedures and such are drifting out of common usage, since it will give you a serious degradation in maintainability.
Re: MySQL IF statement/function
Posted: Tue Feb 10, 2009 8:24 am
by mintedjo
josh wrote:Putting business logic in mysql is not advised
I'm no master of this stuff but I thought it was advised

I've read in several places that stored database procedures are a good way to keep business rules separate from the rest of the application and it doesn't seem like a bad idea to me.
What is it that makes it susceptable to "degradation in maintainability"?
Re: MySQL IF statement/function
Posted: Tue Feb 10, 2009 8:42 am
by VladSun
batfastad wrote:Hi everyone
I've just discovered that MySQL has an IF function

It could be very useful to do some simple conditional numeric calculations in MySQL. Especially where a particular report outputs in several different formats, could cut 50 lines of PHP out of each report
I've seen advices like "Do not use DB as a calculator."
I usually use IF() in "crosstables" - some call it "pivot tables" (I 'm not sure if it's the same)
http://lists.mysql.com/mysql/13989
Re: MySQL IF statement/function
Posted: Tue Feb 10, 2009 8:54 am
by josh
It decreases maintainability because you have business logic in separate places that is highly coupled. Its harder to debug, etc.. etc.. It was advised in the 80s when things like views and stored procedures improved performance. Now it doesnt matter. Not to say they're "always bad" but why have a convoluted SQL query when you can code the same thing cleanly using objects with minimal performance impact? Way easier to maintain IMO
Re: MySQL IF statement/function
Posted: Tue Feb 10, 2009 11:24 am
by batfastad
So by that I guess it's also a bad idea to use any MySQL functions to avoid processing in PHP... UPPER, SUBSTRING etc
I should do all that in PHP instead?
Re: MySQL IF statement/function
Posted: Tue Feb 10, 2009 12:03 pm
by josh
Preferably. Each entity in your system should be an object, and all relevent knowledge about that object should be expressed directly in code in 1 place, its not about code quality as much as its about semantic unambiguity. You don't want to keep modifying data in different layers, its better to express that as domain knowledge directly on the object so it can be re-used in other parts of the system more easily.
Re: MySQL IF statement/function
Posted: Thu Feb 12, 2009 11:17 am
by pickle
Topic split. The new topic is here:
viewtopic.php?f=2&t=95164