php and mysql get data by year and date wise! table structure?

Question

While making a sample details page, I just stuck upon a really confusing situation. What i actually want to do is like this:

+----------------------------------------------------+
+ Year  | Month   | Date        | Total Gain +
+----------------------------------------------------+
+ 2003  | January | 26/01/2003  |  +90 %             + 
+ 2003  | January | 27/01/2003  |  +10 %             +   
+ 2003  | Feburary| 01/02/2003  |  -29 %             +
+ 2003  | Feburary| 15/02/2003  |  +0.52 %           +
+----------------------------------------------------+

What I actually want is that I can list the Month names and year wise like mysql_query('SELECT month FROM mytable WHERE year="2003"');

But the problem is it shows January two time. I want to display January one time and a link next to it which will carry to the next page. This will show the stats of January.

Answer 1

Maybe you should use a GROUP BY clause and calculates your Total gain as well.

(Since the table structure is not specified, I'll guess here)

SELECT `Year`, `Month`, SUM(gain) AS 'Total Gain +' FROM table GROUP BY month, year;

Answer 2

I think what youre looking for is DISTINCT query, ie

SELECT DISTINCT year, month FROM mytable