Group by month/year from DATE FORMAT column

Report · Nov 26, 2009

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:

Code:

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);

Code:

// 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 
  ?>

Code:

// 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>

Code:

// 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?

Report · Nov 26, 2009

If projcompletedate is in DATE format, ORDER BY projcompletedate will put them in the right order.

If you want to use some sort of header in your output to indicate the month and year, use an alias in the SQL to extract the date parts: MONTHNAME(projcompletedate) AS themonth, YEAR(projcompletedate) AS theyear. You can then use conditional logic to display the headings:

// initialize the variables outside the loop
$currentMonth = '';
$currentYear = '';
// start the repeat region
do {
  if ($row_recordsetName['themonth'] != $currentMonth) {
    echo $row_recordsetName['themonth'];
  }
  if ($row_recordsetName['theyear'] != $currentYear) {
    echo ' ' . $row_recordsetName['theyear'];
  }
  $currentMonth = $row_recordsetName['themonth'];
  $currentYear = $row_recordsetName['theyear'];
// rest of repeat region

This checks the values of $currentMonth and $currentYear against the month and year in the current record. If they don't match, they're displayed. Otherwise, they're ignored. The first time the loop runs, both variables are empty, so the values from the first record are displayed. After displaying them, the values from the current record are assigned to the variables ready for comparison when the next record is displayed.

Report · Nov 26, 2009

Hi David,

Thanks for your reply, the MONTHNAME and YEAR alias' work fine, but I can't seem to get the labels/headings to work, a blank page is returned.

This is my table, I want the row below the headers to be the group for Month, but when I try to put your code in, it fails.....can you see what I'm missing?:

<table border="0" cellpadding="0" cellspacing="0" id="tblinvoice">
    <tr>
      <th>Job Title</th>
      <th>Type</th>
      <th>Language</th>
      <th>Translator</th>
      <th>Total</th>
      <th>Full</th>
      <th>Fuzzy</th>
      <th>Proof</th>
      <th>Full Price</th>
      <th>Discount Price</th>
      <th>Document Format</th>
      <th>Pages</th>
      <th>Typesetting Cost</th>
      <th>EN Proofreading Cost</th>
      <th>Total</th>
      <th>Translator Charge</th>
      <th>Profit</th>
      <th>Profit Percentage</th>
    </tr>
    <tr>
      <td colspan="18" class="highlight"> </td>
    </tr>
    <?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
   ?>
   <tr>
    <td colspan="18" 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'] ?> <?php echo $row_rsInvPending['themonth'] ?> <?php echo $row_rsInvPending['theyear'] ?></em></span></td>
    </tr>
    <?php $previousProject = $row_rsInvPending['projid']; } ?>
    <tr class="proj1<?php echo $row_rsInvPending['projid'] ?>" style="display:none">
        <td><?php echo $row_rsInvPending['jobname']; ?></td>
        <td><?php echo $row_rsInvPending['projtype']; ?></td>
        <td><?php echo $row_rsInvPending['langtname']; ?></td>
        <td><?php echo $row_rsInvPending['translator']; ?></td>
        <td></td>
        <td></td>
        <td></td>
        <td></td>
        <td></td>
        <td></td>
        <td></td>
        <td></td>
        <td></td>
        <td></td>
        <td></td>
        <td></td>
        <td></td>
        <td></td>
        <td></td>
      </tr>
      <?php } while ($row_rsInvPending = mysql_fetch_assoc($rsInvPending)); ?>
      <?php } // Show if recordset not empty ?>

Report · Nov 26, 2009

You have got two while statements:

    <?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
[Big snip]

      </tr>
      <?php } while ($row_rsInvPending = mysql_fetch_assoc($rsInvPending)); ?>
      <?php } // Show if recordset not empty ?>

The first while should be "do"

    <?php
      $previousProject = '';
      if ($totalRows_rsInvPending > 0) {
      // Show if recordset not empty
      do {
      if ($previousProject != $row_rsInvPending['projid']) {
      // for every Project, show the Project ID
[Big snip]

      </tr>
      <?php } while ($row_rsInvPending = mysql_fetch_assoc($rsInvPending)); ?>
      <?php } // Show if recordset not empty ?>

Report · Nov 26, 2009

Hi David, I'm not sure if I've got this right, would you mind looking at my latest code?:

<table border="0" cellpadding="0" cellspacing="0" id="tblinvoice">
    <tr>
      <th>Job Title</th>
      <th>Type</th>
      <th>Language</th>
      <th>Translator</th>
      <th>Total</th>
      <th>Full</th>
      <th>Fuzzy</th>
      <th>Proof</th>
      <th>Full Price</th>
      <th>Discount Price</th>
      <th>Document Format</th>
      <th>Pages</th>
      <th>Typesetting Cost</th>
      <th>EN Proofreading Cost</th>
      <th>Total</th>
      <th>Translator Charge</th>
      <th>Profit</th>
      <th>Profit Percentage</th>
    </tr>

    <?php
      $previousMonth = '';
      if ($totalRows_rsInvPending > 0) {
      // Show if recordset not empty
      do ($row_rsInvPending = mysql_fetch_assoc($rsInvPending)) {
      if ($previousMonth != $row_rsInvPending['themonth']) {
      // for every Month, show the Month Name
   ?>

<?php $previousMonth = $row_rsInvPending['themonth']; } ?>

    <?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
   ?>

<?php $previousProject = $row_rsInvPending['projid']; } ?>

    <tr class="proj1<?php echo $row_rsInvPending['projid'] ?>" style="display:none">
        <td><?php echo $row_rsInvPending['jobname']; ?></td>
        <td><?php echo $row_rsInvPending['projtype']; ?></td>
        <td><?php echo $row_rsInvPending['langtname']; ?></td>
        <td><?php echo $row_rsInvPending['translator']; ?></td>
        <td></td>
        <td></td>
        <td></td>
        <td></td>
        <td></td>
        <td></td>
        <td></td>
        <td></td>
        <td></td>
        <td></td>
        <td></td>
        <td></td>
        <td></td>
        <td></td>
        <td></td>
      </tr>

<?php } while ($row_rsInvPending = mysql_fetch_assoc($rsInvPending)); ?>
<?php } // Show if recordset not empty ?>

Report · Nov 26, 2009

No, you haven't got it right. Look at the code I gave you.

do {

That's the complete line. It's a do... while loop (http://docs.php.net/manual/en/control-structures.do.while.php).

Report · Nov 26, 2009

Hi David, I think I see what you're saying, this is basically the type of thing I'm after:

- November, 2009

- Project ID

- Job ID

- December, 2009

- Project ID

- Job ID

- Project ID

- Job ID

I'm not sure how to combine the two commands!

<?php $previousMonth = '';

if ($totalRows_rsInvPending > 0) {

// Show if recordset not empty

do {

if ($previousMonth != $row_rsInvPending['thmonth']) {

// for every Month, show the Month Name

if ($previousProject != $row_rsInvPending['projid']) {
// for every Project, show the Project ID

?>

Sorry about this, as you know, I'm learning on the job!

Report · Nov 26, 2009

    <?php
      $previousMonth = '';
      if ($totalRows_rsInvPending > 0) {
      // Show if recordset not empty
      do  {
      if ($previousMonth != $row_rsInvPending['themonth']) {
      // for every Month, show the Month Name
   ?>
    <tr>
      <td colspan="18" class="highlight"><?php echo $row_rsInvPending['themonth'] ?>, <?php echo $row_rsInvPending['theyear'] ?></td>
    </tr>
    <?php $previousMonth = $row_rsInvPending['themonth']; 
           } ?>
    <tr>
    <td colspan="18" 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> 

    <tr class="proj1<?php echo $row_rsInvPending['projid'] ?>">
        <td><?php echo $row_rsInvPending['jobname']; ?></td>
        <td><?php echo $row_rsInvPending['projtype']; ?></td>
        <td><?php echo $row_rsInvPending['langtname']; ?></td>
        <td><?php echo $row_rsInvPending['translator']; ?></td>
        <td></td>
        <td></td>
        <td></td>
        <td></td>
        <td></td>
        <td></td>
        <td></td>
        <td></td>
        <td></td>
        <td></td>
        <td></td>
        <td></td>
        <td></td>
        <td></td>
        <td></td>
      </tr>
      <?php } while ($row_rsInvPending = mysql_fetch_assoc($rsInvPending)); ?>
      <?php } // Show if recordset not empty ?>

In one of your table rows, you had style="display:none;". That would prevent the whole row from being displayed, even if it had data in it.

Report · Nov 26, 2009

Hi David, that's great, thanks.

I have to keep the <style="display:none"> in place, otherwise a blank row is inserted.

The only problem I'm having now is that if the JobID row is opened, then close the Month row (without closing the Project row), the JobID row remains visible, is that a PHP error or is that a Javascript issue?

Cheers

Samuel

Report · Nov 26, 2009

Unrelated to your question, but you can simplify the query a bit by changing this

AND langtname!='TH'
AND langtname!='ID'
AND langtname!='KO'
AND langtname!='JP'

to 

AND langtname Not In ('TH','ID','KO','JP')

Report · Nov 27, 2009

Hi Chaps,

Got a bit of a problem with the results, the Projects are being grouped by Month, which is correct, but the Jobs aren't being grouped by Project.

- Month, Year

- ProjectA

- Job1

- ProjectA

- Job2

....

ORDER BY projid ASC", GetSQLValueString($colname_rsInvSent, "int"));
$rsInvSent = mysql_query($query_rsInvSent, $conndb2) or die(mysql_error());
//$row_rsInvSent = mysql_fetch_assoc($rsInvSent);
$totalRows_rsInvSent = mysql_num_rows($rsInvSent);
...

What am I missing?

Thanks

Group by month/year from DATE FORMAT column

1 Correct answer