Development
Cannot directly use the IF function on a MySQL query that uses GROUP BY
Cannot directly use the IF function on a query that uses GROUP BY. This tip provides an alternative that is cross-db. It is extracted from: http://surfnet.dl.sourceforge.net/sourceforge/souptonuts/README_mysql.txt SQL Characteristic Functions: Do it without if, case, or GROUP_CONCAT. Yes, there is use for this … if statements sometimes cause problems when used in combination. The simple secret, and it’s also why they work in almost all databases, is the following functions: sign (x) returns -1,0, +1 for values x < 0, x = 0, x > 0 respectively abs( sign( x) ) returns 0 if x = 0 else, 1 if x > 0 or x < 0 1-abs( sign( x) ) complement of the above, since this returns 1 only if x = 0 Quick example: sign(-1) = -1, abs( sign(-1) ) = 1, 1-abs(sign(-1) ) = 0 Data for full example:
Defaulting optional parameters
If you have a function that takes an optional parameter – e.g. function blah($xx,$yy='Default',$zz){ …. You can use the function and ensure that the optional parm always takes the correct default (even if the definition changes) by passing a NULL to it. e.g. … $mydate=blah($something,NULL,$more); …
