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: 23 + 68 = ? followed by the letter: D 

About Me
More Blogs
Main Site
Posts: (year)
2024 (2)
     ffmpeg voodoo
     RegJump Vb
2023 (9)
     VB6 Virtual Files
     File handles across dlls
     python abort / script timeout
     VB6 Python embed w/debugger
     python embedding
     VB6 IDE Enhancements
     No Sleep
     A2W no ATL
2022 (4)
     More VB6 - C data passing
     Vb6 Asm listing
     Byte Array C to VB6
     Planet Source Code DVDs
2021 (2)
     Obscure VB
     VB6 IDE SP6
2020 (4)
     BSTR from C Dll to VB
     Cpp Memory Manipulation
     ActiveX Binary Compatability
2019 (5)
     Console tricks
     FireFox temp dir
     OCX License
     Extract substring
     VB6 Console Apps
2018 (6)
     VB6 UDTs
     VB6 Debugger View As Hex tooltips
     VB6 - C Share registry data
     VB6 Addin Missing Menus
     VB6 Class Init Params
     VB6 isIn function
2017 (6)
     Python and VB6
     Python pros and cons
     download web Dir
     vc rand in python
     VB6 Language Enhancement
     Register .NET as COM
2016 (22)
     VB6 CDECL
     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
2015 (15)
     C# self register ocx
     VB6 Class Method Pointers
     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
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)
     MS GLEE Graphing
     printf for VB6
     C# App Config
     Tero DES C# Test
2012 (10)
     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
2011 (7)
     C# Process Injection
     CAPTCHA Bots
     C# PE Offset Calculator
     VB6 Async Download
     Show Desktop
     coding philosophy
     Code release
2010 (11)
     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
2009 (3)
     The .NET Fiasco
     One rub on computers
     Universal extractor