Which one is better : filter using SQL or PHP?

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_c开发者_如何学JAVAountry'=>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.

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

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.

The database will be a faster filter.

SQL is faster because it is optimized to do so (maybe not your code) and it uses indexes.

I always do as much as I can in SQL. Why grab more information than you need?

There are some rare cases where one would do that in code instead of doing in SQL. For example, the database may be behind a cache, so to keep the cache less cluttered one may apply additional filters on the cached query results. Or a specific querying engine like HandlerSocket may restrict certain queries. Or the database may use indexes not optimally and a developer may not be able to add proper indexes due to security restrictions of some sort.

As you can see these cases are very weird ones, so as a rule you should let the database do what it does best. Minding your question, most of the times this is a bad practice. Don't do it like that.