Which one is better : filter using SQL or PHP?

Question

Situation :

Each user can only see sales report for certain country and certain agent.

So, which one is better :

$reports = $DB->select('fields'=> '*',
                      'table'=>'sales',
                      'where'=>array(
                             'sales_date'=>array(
                                  '2011-06-02', '2011-06-04'),
                             'sales_country'=>array_keys($allow_country),
                             'sales_agent'=>array_keys($allow_agent)
                      ));

Or :

$result = $DB->select('fields'=> '*',
                      'table'=>'sales',
                      'where'=>array(
                             'sales_date'=>array(
                                  '2011-06-02', '2011-06-04')
                      ));

while (($row=mysql_fetch_assoc(result)) != null) {
 if (array_key_exists($row['country'], $allow_country) && array_key_exists($row['agent'], $allow_agent){
  $reports[] = $row;
 }
}

in terms of good practice and processing time?

Note : My DB class use php prepared statement.

Answer 1

Databases are optimized for doing this sort of thing. Don't do it in code.

Answer 2

There are (at least) two separate issues here:

• CPU time. As others have said, you'll probably find that the database is better at performing this sort of operation.
• Bandwidth. The second method requires sending a much larger dataset back to the web server. So if your database server and web server are separate machines, this could slow down the request.