Query Last 12 Mos


Author: Dave
Date: 12.02.14 - 4:39am



for my web humidor project I wanted to generate a bar graph with the last 12 months of data. I wanted the data to display in a rolling format where the current month was at the rightmost position then going back in time for the 12 previous months.

I do not use date-time functions very often and wasn't sure how to do this. Below is a snippet of code I cooked up for the task. It took a little bit of fidgeting to get right so thought I would share it.

in this example 3 empty by reference arrays are passed in, these are the out values and would end up being, separated value list of the data I wanted for the bargraph.

//show rolling stats for last 12 mos, out args by ref 
// month names csv, smoked events csv, water events csv 
function eventsfor_last12Months(&$d,&$s,&$w){

        $mname = array(0, 'Jan','Feb','Mar','Apr','May','June','July','Aug','Sept','Oct','Nov','Dec', 14);
	$y=0; 
	$year_index=0;
	$month = date("m"); //start month number
	
        for($i=12; $i>0; $i--){
		$mi = $month - $y; //month index
		
		if($mi <= 0){
			 $mi += 12; 
			 $year_index = 1; //we have crossed into previous year now...
		}
		
		//echo $mi. ",";
		$d[$i-1] = $mname[$mi];
		
		$r = mysql_query("SELECT count(autoid) as c FROM humidor WHERE MONTH(tstamp) = $mi AND 
                                 YEAR(tstamp) = (YEAR(NOW()) - $year_index) and smoked=1");

		$rr = mysql_fetch_assoc($r);
		$s[$i-1] = $rr['c'];
		
		$r = mysql_query("SELECT count(autoid) as c FROM humidor WHERE MONTH(tstamp) = $mi AND 
                                 YEAR(tstamp) = (YEAR(NOW()) - $year_index) and watered=1");

		$rr = mysql_fetch_assoc($r);
		$w[$i-1] = $rr['c'];
		
		$y+=1;
	}
	//echo var_dump($d);
	$d = arytocsv( $d, 1 );
	$s = arytocsv( $s );
	$w = arytocsv( $w );
}





Comments: (0)

 
Leave Comment:
Name:
Email: (not shown)
Message: (Required)
Math Question: 57 + 82 = ? followed by the letter: S 



About Me
More Blogs
Main Site
Posts: (All)
2020 ( 3 )
2019 ( 5 )
2018 ( 6 )
2017 ( 6 )
2016 ( 22 )
2015 ( 16 )
2014 (25)
     Query Last 12 Mos
     Progid from Interface ID
     VB6 to C Array Examples
     Human Readable Variant Type
     ScriptBasic COM Integration
     CodeView Addin
     ScriptBasic - Part 2
     Script Env
     MSCOMCTL Win7 Error
     printf override
     History Combo
     Disable IE
     API Hooking in VB6
     Addin Hook Events
     FastBuild Addin
     VB6 MemoryWindow
     Link C Obj Files into VB6
     Vb6 Standard Dlls
     CStr for Pascal
     Lazarus Review
     asprintf for VS
     VB6 GlobalMultiUse
     Scintilla in VB6
     Dynamic Highlight
     WinVerifyTrust, CryptMsgGetParam VB6
2013 ( 4 )
2012 ( 10 )
2011 ( 7 )
2010 ( 11 )
2009 ( 3 )