Show data for each month

I have a table with 40 or so rows - here's a small sample:

ID Date         Name      apples
1   1284497100    Brian     2
2   1288126800    Tom       5
3   1290268800    Kirsten   3
4   1292274000    Emma      7
5   1294174800    Sophie    1
6   1299012300    Lars      3

The dates are spread over a year or so, and what I would like is to have a PHP page showing me the two people with most apples in each month:

January:

Name1

Name2

February:

Name1

Name2

Etc.

I was wondering if there's a simple way to do this, or if I have to create 12开发者_运维百科 different mysql-codes for each month (which I assume takes it's toll on the server - am I right?)

Simplest way:

SELECT
  EXTRACT(YEAR_MONTH FROM FROM_UNIXTIME(`Date`)) AS year_mo,
  `Name` AS name,
  SUM(`Apples`) AS sum_apples
FROM apples_table
GROUP BY 1,2
ORDER BY 1,3 DESC;

You should probably be using a DATETIME column to store your dates, but you can use the FROM_UNIXTIME() function to convert the values for use with EXTRACT. See the MySQL date/time functions documentation for more info.

This query will give you more than two rows per month, which you would handle in the PHP loop that reads the results by keeping track of how many consecutive times you see the same month and discarding rows as necessary. I think this is a much better way to handle your problem than using 12 separate queries.

With only 40 rows in your table, performance isn't going to be an issue unless you do something really bad.

If you really want to delve into the arcane SQL syntax needed to return exactly the results you want in one query, have a look at this article. Handling ties and other edge cases will be messy this way.

Edit - here's the idea of the PHP code:

$query = mysql_query(<<<SQL
  SELECT
    EXTRACT(YEAR_MONTH FROM FROM_UNIXTIME(`Date`)) AS year_mo,
    `Name` AS name,
    SUM(`Apples`) AS sum_apples
  FROM apples_table
  GROUP BY 1,2
  ORDER BY 1,3 DESC;
SQL
) or die(mysql_error());

$last_year_mo = '';
$same_date_count = 0;
while ($row = mysql_fetch_assoc($query)) {
  # Only process 2 rows for each date
  if ($row['year_mo'] == $last_year_mo) {
    if (++$same_date_count >= 2) {
      continue;
    }
  } else {
    $last_year_mo = $row['year_mo'];
    $same_date_count = 0;
    # Spit out some HTML if needed, like a separator
  }

  # year_mo is a string in the form YYYYMM
  $year = substr($row['year_mo'], 0, 4);
  $month = date('F', mktime(0, 0, 0, substr($row['year_mo'], 4, 2), 1));

  # Spit out some HTML for this row
}