Concatenated query or loop result

Question

What would be better? (consider performance, maintainability, readability)

pseudo code:

SELECT CONCAT('<td>', user_firstname, '</td>') FROM TableA; 

OR

 $results = SELECT user_firstname FROM TableA foreach($result as $fname) { $row .= '<td>'.$fname.'</td>'; } 

It seems to me that doing this in a query would be better, because PHP would not have to iterate over it (suppose ~ 20,000 rows in TableA). But I also think that anyone who sees this code in the future is wondering why you will do this in the request.

EDIT: The main reason I ask is our php loop elsewhere, because this dataset is very expensive.

Answer 1

I would prefer to return simple data and manipulate it with PHP. In any case, you will undoubtedly iterate over the results, so why not just drop the tags in both directions in the interval of time?

This, however, is not my main argument - if you end up using the same data for multiple instances, you can cache and reuse the results by embedding the returned data in different templates in accordance with the usage requirements (not possible if the returned the data set has a viewing specific code for each row).

In addition, the request should be separated from the code that creates the view in any case, so when updating the final markup it will be easier to modify PHP, which creates the markup (in the same file as the other markup), than searching for a request to change the markup. echo '<th>'.$field.'</th>'; also much clearer than his intentions, not just echo $field; .

In the end, it depends on personal preferences, but, as in good practice for MVC structures and usually clean and maintained code, I will definitely vote for the markup that needs to be done in PHP

TL DR

• Use PHP - you might still get stuck with it anyway
• echo '<th>'.$field.'</th>'; has clearer intentions (more readable) than echo $field;
• If the data should be used elsewhere, you can cache one request for reuse with another markup template, instead of having to make multiple requests each time by adding different wrapped tags
• Databases are best left to search for data for readability (hence MVC relocation), then this data can be processed in a language more suitable for the job, such as PHP

Answer 2

If you are returning 20,000 rows, you might still have to scroll through them. If so, there is a minimal performance difference between working in php or MySQL.

The benefit of this in MySQL is that a database can trivially do this type of work, and many databases can take advantage of multiple processors to speed things up. I prefer to return results as they are needed when it's easy.

The benefit of this in php is that you return fewer characters from the database, so there may actually be a slight performance boost over the network. This will be offset by work from the application side.

I would like to do this in the database for maintenance and reading reasons. The logic for extracting data is in one place, not in two, and is expressed in one language, not two.

Answer 3

do it at the php end - why? so that your data layer is separate from the presentation layer. SQL must have one job and only one job. That is, request and retrieve raw data. Secondly, you start by saying that SQL does stuff that it is not intended to do - this is the second, your application starts to go down and starts to become a pain to maintain. Use the servers for the purpose and retrieve your data from SQL, rather at the end of the web server (translate it into tables, etc.).