MySQL SELECT (many times) or SELECT once, then use the array as a variable

Question

For example, the MySQL table for invoices (date, amount, customer name, etc.).

The user wants to select (display / echo) invoices issued for one specific month (for example, April).

The user then decides to display the invoices issued in April and to one specific customer.

I assume there are 2 options:

1) at each MySQL SELECT query and selection of the required data, or

2) only one MySQL SELECT and selection; create an array with the extracted results; then use / process array

Question: which option would be better for performance? Many MySQL SELECTs use server resources; but, on the other hand, the array also uses resources to store in memory. Which option uses less resources?

Answer 1

Always use a database to retrieve data. Try to get the smallest amount you can by grouping and summing / calculating on the database. Use LIMIT etc. For swap. This way you do not need to have a large array.

If you process large sets, then extract them in chunks, not in a string. Sorry - not a PHP expert, so I can’t be sure of the exact expression for PHP, but I think mysql_fetch_assoc is what you need.

Answer 2

I need how much data you should get from the database for all month invoices. But I would usually use a MySQL query filtering the results. Mysql queries are much faster and better optimized when filtering results than when using php.