Show data for every month

Question

I have a table with 40 or so rows - here is a small example:

  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 

Dates apply for a year or so, and I would like the PHP page to show me two people with most apples in each month:

January:
Name1
Name2

February:
Name1
Name2

Etc.

I was wondering if there is an easy way to do this, or if I need to create 12 different mysql codes for each month (which I assume is causing damage to the server - am I right?)

Answer 1

The 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 use the DATETIME column to store your dates, but you can use the FROM_UNIXTIME() function to convert values ​​for use with EXTRACT . See the MySQL date / time documentation for more information.

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

If your table has only 40 rows, performance will not be a problem unless you do something really bad.

If you really want to delve into the secret SQL syntax needed to get exactly the results you want in a single query, see this article . Handling tacks and other marginal cases will be so messy.

---

Edit is the idea of ​​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 }