[SOLVED] Problem with Yearly Increment and Corresponding #of entries.

[pets2soul]

Wazzzup people,

 

I'm trying to make a index sort of page, which will categorize the database entries into years,

from 2006 to date("y")-1. Also I want to show the number of entries in each year.

 

This is probably simple, but I'm jammed some where...below is my code, please kindly let me

know how should I correct and improve it. Thank you belly machi!

 

<?php
echo "<table width='300' border='0' cellspacing='0' cellpadding='0'>
<tr>
<td colspan='3' height='38' align='left' valign='top'>
<a href='dataentry.php'>Add</a>
<a href='datalist.php'>List</a>
<a href='annuallist.php'>Annual</a></td>
    </tr>";

$con=mysql_connect("servername","username","password") or
die('Could not connect: '.mysql_error());

mysql_select_db("dbname",$con);

$start_y='2006';
$end_y=date("y")-1;

for($i=$start_y; $i<$end_y; i++){

  $year="'$i-01-01' AND '$i-12-31'";

  $q = "select count(*) as num_ent1 from table WHERE date BETWEEN $year";
  $res = mysql_query($q) or die('Problem with query: ' . $q . '<br>' . mysql_error());
  $rw = mysql_fetch_assoc($res);

  echo "<tr>
  <td width='100' height='28'>Year</td>
  <td width='100'># of Entries</td>
  <td width='100'>Enter</td>
  </tr>";

    echo "<tr>";
    echo "<td height='28'>".$i."</td>";
    echo "<td>".$rw['num_ent1']."</td>";
    echo "<td><a href=dataedit.php?".$i.">Go</a></td>";
    echo "</tr>";
  }
echo "</table>";

mysql_close($con);
?>

[sasa]

 

SELECT COUNT( * ) as num_ent1, DATE_FORMAT( `date` , '%Y' ) AS y

FROM `table`

WHERE 1

GROUP BY y

HAVING y >2005 and y<2008

[pets2soul]

Great! It's working now!! Thanks, sasa!