Group by month/year from DATE FORMAT column

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
koolsamule
Forum Contributor
Posts: 130
Joined: Fri Sep 25, 2009 10:03 am

Group by month/year from DATE FORMAT column

Post by koolsamule »

pickle | Please use [ code=php ], [ code=text ], etc tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: :arrow: Posting Code in the Forums to learn how to do it too.


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 all

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: Select all

// 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: Select all

// 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>&nbsp;<?php echo $row_rsInvPending['projid'] ?>&nbsp;-&nbsp;</a></span><span class="blueNOTBold"><em><?php echo $row_rsInvPending['projtitle'] ?></em></span></td>
    </tr>

Code: Select all

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


pickle | Please use [ code=php ], [ code=text ], etc tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: :arrow: Posting Code in the Forums to learn how to do it too.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Group by month/year from DATE FORMAT column

Post by josh »

You could make an indexed version of the table or a view (or in some way seperated the "index" data from your live production data), that has a column that stores the month & year

column monthyear = '06-1999'
column year = '1999'

in this way you could have a column you can use for each grouping style, and mysql can take advantage of better indexing.

This is basically called a star schema, or dimensional modeling. http://en.wikipedia.org/wiki/Star_schema It intentionally introduces de-normalizing to avoid un-manage-able sql and performance issues. Within each dimension table you might have columns "at different rollup levels", like monthyear and year.

So you would have a time dimension, with rollups at the month and year level. Your main fact table would have a foreign key

so selecting all columns from the time dimension where the year was 1999 might give you 12 rows[1] from the time table
1,2,3,4,5,6,7,8,9,10,11,12
Then you would select from your fact table where the time_id was IN (1,2,3,4,5,6,7,8,9,10,11,12)

so selecting all columns from the time dimension where the monthyear was '12-1999' would give just 1 row
12
Then you would select from your fact table where the time_id was IN ( 12 )



Make sense? In this way you can rollup/group your data at unlimited different granularities ( showing all data that occured on weekends, grouping by season, grouping by quarter, grouping by week #, etc.. ) in a very scalable manner.

[1](since the granularity is at the month level and there are 12 months in the year 1999)
koolsamule
Forum Contributor
Posts: 130
Joined: Fri Sep 25, 2009 10:03 am

Re: Group by month/year from DATE FORMAT column

Post by koolsamule »

Hey dude, thanks for the reply, that looks like it would do the trick, the only thing is, the we app is up and running and 'live'.

The company I work for, constantly want things updating and changing and tbl_projects has a DATE column projcompletedate with plenty of data already in, so I'm not sure if I can design the table at this point, create a DIMENSION table for the tim/date, then link the two together.

Is there a way of 'exploding' the projcompletedate (y-m-d), so it gives me the year and month, then I can use PHP to group them together?
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Group by month/year from DATE FORMAT column

Post by josh »

koolsamule wrote:Is there a way of 'exploding' the projcompletedate (y-m-d), so it gives me the year and month, then I can use PHP to group them together?
Is that rhetorical question? Or are your asking about the implications of such an approach?
koolsamule
Forum Contributor
Posts: 130
Joined: Fri Sep 25, 2009 10:03 am

Re: Group by month/year from DATE FORMAT column

Post by koolsamule »

No, just asking if there is a way of possibly doing it that way, rather than changing the design of the table?
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Group by month/year from DATE FORMAT column

Post by josh »

Yeah I guess. FYI you don't have to change the design of the table. You could create a view, or you could alter the code such that on every modification of that record it updates an "index" table somewhere else, which would contain the same data in more easily queryable formats (denormalized)
Post Reply