Separating Logic - With a typical LAMP stack, should PHP or MySQL take care of this example? [closed]

Closed. This que开发者_如何学Cstion is opinion-based. It is not currently accepting answers.

---

Want to improve this question? Update the question so it can be answered with facts and citations by editing this post.

Closed 8 years ago.

Improve this question

This question made me curious.

The correct answer demonstrates using MySQL syntax to add values of a certain range within a database and return the result. Would it be better done retrieving the results within the daterange and computing the total in PHP? Or should the DB be used wherever applicable? Obviously with the example given in that question, it won't really matter unless we're talking thousands of orders, but for arguments sake...

---

Simple calculations, especially for aggregation type scenarios, should definitely be handled on the database for multiple reasons:

1. Less data returned over the wire from the database
2. Database can take advantage of indexing
3. Database is faster at aggregating data, because that's what they're made for.

Using code such as PHP makes sense only when you have really complex calculations or business logic that are not handled easily by a database tool, or when using logic that databases do not do efficiently, such as string manipulation.

The general rule of thumb is something like this:

1. Return as little data as absolutely necessary from the database, and apply any logic that reduces the number of rows at the database side.
2. Work with the data returned to do complex business logic and markup (i.e., HTML) with your coding language.

---

Why would you rewrite code? SUM(somefield) WHERE (condition) gives you exaclty what you need.

$rows = mysql_query('SELECT SUM(somefield) AS `sum` FROM table WHERE (condition)');
$row = mysql_fetch_array($rows);
$sum = $row['sum'];

On the contrary, you suggest to do something like this:

$rows = mysql_query('SELECT somefield FROM table WHERE (condition)');

$sum = 0;

while ($row = mysql_fetch_assoc($rows)) {
    $sum += $row['somefield']; }
}

I don't see why. ;-)

And that's pure for the code, indeed more data is being transported in the second case, to name one thing.