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);
	$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'];
	//echo var_dump($d);
	$d = arytocsv( $d, 1 );
	$s = arytocsv( $s );
	$w = arytocsv( $w );

Comments: (0)

Leave Comment:
Email: (not shown)
Message: (Required)
Math Question: 41 + 77 = ? followed by the letter: T 

About Me
More Blogs
Main Site
Console tricks
FireFox temp dir
OCX License
Extract substring
VB6 Console Apps
VB6 Debugger View As Hex tooltips
VB6 - C Share registry data
VB6 Addin Missing Menus
VB6 Class Init Params
VB6 isIn function
Python and VB6
Python pros and cons
download web Dir
vc rand in python
VB6 Language Enhancement
Register .NET as COM
UDT Tricks pt2
Remote Data Extraction
Collection Extender
VB6 FindResource
DirList Single Click
Reset CheckPoint VPN Policy
VB6 BSTR Oddities Explained
SafeArrays in C
BSTR and Variant in C++
Property let optional args
Misc Libs
Enum Named Pipes
Vb6 Collection in C++
VB6 Overloaded Methods
VB6 Syncronous Socket
Simple IPC
VB6 Auto Resize Form Elements
Mach3 Automation
Exit For in While
C# self register ocx
VB6 Class Method Pointers
JS Debugger
Duktape Debug Protocol
QtScript 4 VB
Vb6 Named Args
vb6 Addin Part 2
VB6 Addin vrs Toolbars
OpenFile Dialog MultiSelect
Duktape Example
DukTape JS
VB6 Unsigned
.Net version
TitleBar Height
.NET again
VB6 Self Register OCXs
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
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
MS GLEE Graphing
printf for VB6
C# App Config
Tero DES C# Test
VC 2008 Bit Fields
Speed trap
C# Db Class Generator
VB6 vrs .NET (again)
FireFox Whois Extension
git and vb6
Code Additions
Compiled date to string
C# ListView Sorter
VB6 Wish List
C# Process Injection
C# PE Offset Calculator
VB6 Async Download
Show Desktop
coding philosophy
Code release
Dll Not Found in IDE
Advanced MSScript Control
random tip
Clipart / Vector Art
VB6 Callback from C#
Binary data from VB6 to C#
CSharp and MsScriptControl
HexDumper functions
Js Beautify From VB6 or C#
vb6 FormPos
Inline Asm w VB6
The .NET Fiasco
One rub on computers
Universal extractor