Conceptually this is the method for myparses’ database structure and sql interactions. The idea here is to have two tables, one that acts as an optional container for the templates, and contains information such as what template (if any) to use, a method to return the data (URL) and other attributes (content etc.). And to have another table that acts as the template container, which consists of the same structure minus some options that are to only be provided in the first table (rendering order, display method).
Instead of handing this processing off to a scripting language most can be accomplished within the sql statement, and the scripting language will not need to distinguish between a ‘template’ database-object, and a normal object. All it sees is a refined, joined set of objects with the appropriate fields selected depending on the access method (url).
IFNULL()
The coalesce and ifnull mysql functions behave the same way. Except, with coalesce you can have more than two database fields or values.
example (in the first block of a sql statement)
IFNULL(t.master_select,b.master_select) as master_select
Using IFNULL will return the second value if the first value is equal to null. If both values are null, null will be returned. IFNULL shines if you need to replace a value with another, if it exists when you join two tables.
Coalesce()
Coalesce will return the first value in the list that is not null. This function is slower than IFNULL, but probably more useful than nesting multiple IFNULL statements (or maybe using a NULLIF inside of IFNULL might be faster) but regardless syntax would be hard to read and troubleshoot that that point.
Using NULLIF() instead of IF()
Example
IF(b.urls != ‘*’,'1′,) as unique_block_exists
Becomes
NULLIF(b.urls,’*') as unique_block_exists
NULLIF is extremely useful for replacing IF() statements that process simple true/false logic. Simply compare two values, if the values match, NULL is returned , if it doesn’t it’ll return the value of the first argument. In your desired scripting language you can simply check to see if the value is null or not. If it is null then your statement succeeded.
You could also have the NULLIF statement contain a subquery to provide conditions based on another linked data-set, although you should take care to only select one record in this fashion as multi-row results will throw errors.
These functions can turn complex database table interactions into a few lines of sql and results in easier code integration from returned records.
mysqltemplate
processing
coalesce
nullif
ifnull
if
mysql function
