I was wondering if there is a best practice regarding the formatting of SQL output. Is it better to do this on PHP side or in the SQL statement itself ? My SGBD is MySQL.
-
What do you call formatting? ordering it or removing certain columns? Please be more descriptive and tell us what you want to do.Manuel– Manuel2012-05-09 10:28:30 +00:00Commented May 9, 2012 at 10:28
Add a comment
|
4 Answers
Unless your formatting affects ordering (see below), you're probably better of doing all the formatting in PHP.
- Passing complex/formatted data though all the application layers leaves you with a handicap of a sort. You should rather pass simple/primitive values and format them just before displaying them to the user. This way, you can use these values for other purposes, not only for display.
The primary reason you'll want to format your data in SQL is when it affects item selection or order, for example:
SELECT complex_expression(x) as fx FROM t ORDER BY fx LIMIT 20
- Doing math in SQL is useful when it somehow limits or orders data in the returned dataset. If you think you can do the same in PHP, you probably can, but it's usually much easier in SQL.
Comments
What do you mean with "formatting"?
If your question is about way of displaying data (data format, currency format, etc.), use PHP for formatting. Formatting is responsibility of presentation layer.
If you need some additional computations or if you need aggregation results like summaries, the best way is usually to use database features. You don't need to transport lots of data to application server, you spare its memory and processor.
2 Comments
Pavel Hodek
Definitely yes, computing is job for a database and formatting is job for presentation layer.
nonesk
I was mainly thinking about basic formating concerning date / currencies presentation. Thanks !
Depends on what kind of formatting you need. Do your math in MySQL. Is faster than using similar PHP functions. If you need basic date formatting you can do it in MySQL, if you need advanced formatting you're better of doing it with PHP.
2 Comments
user254875486
Do you have a link to an article or something on the performance differences? (not that I don't believe you, just out of curiousity)
Daan Geurts
Well if you have a lot of complex result data and complex queries you are better of using PHP.
I use mysql for small logic for example you need counting, adding and these kind of stuff..
If you need to work out complex calculations I would use PHP to not overload the SQL server
