Skip to main content
A
Anonymous
November 26, 2009
Answered

Group by month/year from DATE FORMAT column

  • November 26, 2009
  • 2 replies
  • 730 views

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?

This topic has been closed for replies.
Correct answer David_Powers

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

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


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

2 replies

B
bregent
Participating Frequently
November 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')

A
Anonymous
November 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

David_Powers
David_Powers
Inspiring
November 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.

A
Anonymous
November 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 ?>

David_Powers
David_PowersCorrect answer
Inspiring
November 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

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


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

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices