Group by month/year from DATE FORMAT column

[kool_samule]

Hi Chaps,

I have a recordset that pulls data from a database, then presents the data in a table. The data is grouped by 'projid', repeated for every 'projid', with a show/hide control, to show all the 'jobid's' relating to that particular 'projid'

This is what I have so far:

SELECT              
tbl_projects.projid, 
tbl_projects.projtitle, 
tbl_projects.projdue, DATE_FORMAT(tbl_projects.projdue, '%%d/%%m/%%Y') as projdue_format, 
tbl_projects.projtype,  
tbl_projects.projinvtype, 
tbl_projects.FK_custid, 
tbl_projects.projcompletedate
tbl_languaget.langtname,
tbl_doctype.doctypename,
tbl_jobs.jobid, 
tbl_jobs.FK_projid,               
tbl_jobs.jobname,               
tbl_jobs.FK_langid,               
tbl_jobs.jobpages,               
tbl_jobs.jobshipped, 
tbl_jobs.jobinvsent,    
tbl_jobs.jobtranslatorcharge,     
'tbl_jobs' as fromtable,
tbl_customers.custid,
FROM tbl_projects   
INNER JOIN tbl_jobs               
ON tbl_projects.projid=tbl_jobs.FK_projid   
INNER JOIN tbl_languaget               
ON tbl_languaget.langtid=tbl_jobs.FK_langid   
INNER JOIN tbl_customers               
ON tbl_customers.custid=tbl_projects.FK_custid 
INNER JOIN tbl_costs
ON tbl_costs.FK_custid=tbl_customers.custid  
INNER JOIN tbl_doctype
ON tbl_doctype.doctypeid=tbl_jobs.FK_doctypeid
WHERE tbl_projects.projstatus='Complete'         
AND tbl_projects.projinvtype='Costing Sheet'
AND langtname!='TH'
AND langtname!='ID'
AND langtname!='KO'
AND langtname!='JP'
AND jobinvsent='y'
AND FK_custid = %s
ORDER BY projid ASC", GetSQLValueString($colname_rsInvPending, "int");
$rsInvPending = mysql_query($query_rsInvPending, $conndb2) or die(mysql_error());
//$row_rsInvPending = mysql_fetch_assoc($rsInvPending);
$totalRows_rsInvPending = mysql_num_rows($rsInvPending); 

// REPEAT - FOR EVERY PROJECT
  <?php
  $previousProject = '';
  if ($totalRows_rsInvPending > 0) {
  // Show if recordset not empty
    while ($row_rsInvPending = mysql_fetch_assoc($rsInvPending)) {
      if ($previousProject != $row_rsInvPending['projid']) {
      // for every Project, show the Project ID 
  ?>

// SHOW/HIDE CONTROL
    <tr>
    <td colspan="9" class="highlight"><span class="blueBold"><a href="#" onclick="toggle2('proj1<?php echo $row_rsInvPending['projid'] ?>', this)"><img src="../../Images/plus.gif" border="0" /></a> <?php echo $row_rsInvPending['projid'] ?> - </a></span><span class="blueNOTBold"><em><?php echo $row_rsInvPending['projtitle'] ?></em></span></td>
    </tr>

// SHOW/HIDE 
    <?php $previousProject = $row_rsInvPending['projid']; } ?>
    <tr class="proj1<?php echo $row_rsInvPending['projid'] ?>" style="display:none">
    <td>column 1</td>
    <td>column 2</td>

What I want, is to put in another grouped by stage, where the 'projid's' themselves are in a show/hide region, grouped by 'projcompletedate' (year/month). 'projcompletedate' is in DATE format, but how to I get PHP/SQL to take the month and year and then group them correctly?