MySQL IF statement/function

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

MySQL IF statement/function

Post by batfastad »

Hi everyone
I've just discovered that MySQL has an IF function :lol:
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
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: MySQL IF statement/function

Post 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.
mintedjo
Forum Contributor
Posts: 153
Joined: Wed Nov 19, 2008 6:23 am

Re: MySQL IF statement/function

Post 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 :-P
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"?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL IF statement/function

Post by VladSun »

batfastad wrote:Hi everyone
I've just discovered that MySQL has an IF function :lol:
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
There are 10 types of people in this world, those who understand binary and those who don't
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: MySQL IF statement/function

Post 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
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Re: MySQL IF statement/function

Post 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?
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: MySQL IF statement/function

Post 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.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: MySQL IF statement/function

Post by pickle »

Topic split. The new topic is here: viewtopic.php?f=2&t=95164
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply