Displaying results depending on its date (month & year)

I have load of data in the DB and each row has a date column

Currently im pulling all the results onto a page but i want to split it up into months. I have buttons on my page that when clicked should display only the appropriate results, for example when the button January 2012 is click all the results for that month will be displayed

Heres an example of what im trying to achieve: http://i.stack.imgur.com/PY7iN.jpg

=================================================================================

<?php
$con = mysql_connect("localhost", "username", "pass");
if (!$con)
{
 die('Could not connect: ' . mysql_error());
}

mysql_select_db("database", $con);
$result = mysql_query("SELECT * FROM tablename"); 

$num_rows = mysql_num_rows($result);

echo "<table border='0' cellspacing='0'>";

while($row = mysql_fetch_array($result))
{
$i++;
if($i%2==0) $class="cell1"; else $class="cell2";
echo "<tr class='$class'>";
echo "<td>".$row["firstname"]." ".$row["lastname"]." thinks that it will happen on      
<span class=datecolor>".开发者_如何学Cdate('l jS F Y',strtotime($row['date']))."</span></td>";
 echo "</tr>";
 }  

  echo "</table>";

  mysql_close($con);
  ?> 

=========================================================================

Im looking for a little help on how i can display the results for each month by clicking on the buttons without it appearing all at the one time.

Also the when the page is first viewed id like it to automatically show the results for the current month, for example it if viewed now it would display the August results

Hope you can help

To address your first question (displaying one month at a time), your best bet is to make your database do all of the work for you. Right now your SQL SELECT statement looks like

SELECT * FROM tablename

You want to add a WHERE clause that will restrict this query to only show you rows for a certain month… something like

SELECT * FROM tablename WHERE date BETWEEN '05/01/2011' AND '05/31/2011'

You may need to tweak this for your particular database engine or setup. Here's one WHERE tutorial; you can find tons more on the web.

You can have all your buttons be links to the page with a query string that specifies the month (for example: www.mysite.com/mypage?month=september). Then get the month from the query string and only select the rows that are in that month.

See:

PHP parse_str()

$_SERVER['QUERY_STRING']

MySQL Select a Date Range

Something like

$month = isset($_GET['month']) ? intval($_GET['month']) : 0;
$year = isset($_GET['year']) ? intval($_GET['year']) : 0;

if( empty($month) ){
    $month = date('n');
}
if( empty($year) ){
    $year = date('Y');
}

$query = mysql_query('SELECT * FROM table_name WHERE MONTH(date_field)="'.$month.'" AND YEAR(date_field)='.$year.'"');
// or maybe use BETWEEN syntax

while( false!==($row=mysql_fetch_assoc($query)) ){
    // do something...
}

Year is needed because one month number may belong to diffent year number (03-2010,03-2011 etc..), and, so when your data covers dates from different years, you cannot determine what data you need only having month