A common and useful paradigm of programming is to separate content and presentation. One system deals with building the result, another system deals with outputting it.
You see this with HTML vs CSS. HTML should ideally be clean and without any internal styling (color, position, etc), which should be handled by CSS.
I'm now wondering how this applies to SQL results. I often find myself formatting results in a query:
SELECT CONCAT(first_name, ' ', last_name) AS name, DATE_FORMAT(last_edit, '%d/%m/%Y') AS last_edit, CONCAT( record_type, IF (private = 1, ' (Private)', ' (Public)') ) AS record_type FROM people ORDER BY updated DESC, LastEdit DESC
Three different scenarios are covered here:
- The first column is a simple concatenation.
- The second transforms the output into a different format.
- The third actually adds content to the result.
I understand that there are many useful functions for transforming data in a query. But from a team development point of view, should I be formatting these results in the presentation layer rather than the logic layer (i.e., in my PHP code instead of the SQL query)?
If so, then how far should I take it? Are columns 1 and 2 acceptable but 3 isn't?
Is there a widely-accepted best practice regarding this?