Help with a date query

I need help with a query.  I need to be able to pull dates from current year to last year in the database (so that it can be used by multiple users for my system). I'm trying to populate a select jump menu with dates. I would like to run a query and have it loop through the select feild due to the dates in the database (current year all the way to the earliest year in the database). How in the hell do you do this?
<cfparam name="URL.cDate" default="#Year(Now())#">
<cfset CurrentYear = Year(Now())>
<cfset FourYearsAgo = CurrentYear - 4>/////// or earliest year in database/////////
 
<cfoutput>
	<form action="#CGI.SCRIPT_NAME#" method="get">
		<select name="cDate" id="cDate" onChange="this.form.submit();">
    		<option value=""></option>
			<cfloop from="#CurrentYear#" to="#FourYearsAgo#" index="yearNum" step="-1">
	    	<option value="#yearNum#" <cfif yearNum eq URL.cDate>selected</cfif>>#yearNum#</option>
	    	</cfloop>
		</select>
	</form>
</cfoutput>

Open in new window

LVL 1
jasch2244Asked:
Who is Participating?
 
usachrisk1983Connect With a Mentor Commented:
Are you asking for the select box to contain a list of all the years in the database, from oldest to current?  If so, the code below should give you the query you can use to populate the box.  I'm assuming MSSQL, if it's another DBMS, it will vary.


<cfquery name="qryGetOldestYear" datasource="yourDSN">
     select year(min(yourDateField)) as oldyear
       from yourTable
      group by yourDateField
</cfquery>
 
<!--- Your other code here --->
 
<select name="cDate" id="cDate" onChange="this.form.submit();">
     <option value=""></option>
     <cfloop from="#Year(now()#" to="#qryGetOldestYear.oldYear#" index="yearNum" step="-1">
          <option value="#yearNum#" <cfif yearNum eq URL.cDate>selected</cfif>>#yearNum#</option>
     </cfloop>
</select>
 
<!--- Your other code here --->

Open in new window

0
 
jasch2244Author Commented:
I'm using MySQL will it make a differnece? I'm still a newb
0
 
usachrisk1983Commented:
The MIN and YEAR functions are supported by MySQL, so it should work fine.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
_agx_Commented:
>  CurrentYear - 4>/////// or earliest year in database/////////

    To do that, you would need a slight modification of usachrisk1983's suggestion. Something like this
    would return the earliest year in the database OR 4 years ago.  


      <cfquery name="qryGetOldestYear" datasource="yourDSN">
          SELECT  Year(Min(YourDateColumn)) AS MinYear
          FROM    YourTable
          WHERE   YourDateColumn IS NOT NULL
          GROUP  BY YourDateColumn
          UNION
          SELECT   Year(Date())
          ORDER BY MinYear ASC
          LIMIT 1
</cfquery>
0
 
jasch2244Author Commented:
usachrisk1983:

I keep getting the values #yearNum# for option tag values. This is the index but apparently is not relating to the year (can't have a number as a variable for the index"" portion. If I can somehow get that to come down from 2009 (now() function). I hope this make sense.
0
 
_agx_Commented:
Here is better example that works with MySQL:
<cfparam name="URL.cDate" default="#Year(Now())#">
 
<cfquery name="getData" datasource="YourMYSQLDataSource">
   SELECT  Year(MIN(YourDateColumn)) AS MinYear 
   FROM    YourTable
   WHERE   YourDateColumn IS NOT NULL
   UNION
   SELECT Year(CurDate())-4 AS MinYear 	
   ORDER BY MinYear ASC
   LIMIT 1	
</cfquery>
 
<cfoutput>
<form action="#CGI.SCRIPT_NAME#" method="get">
   <select name="cDate" id="cDate" onChange="this.form.submit();">
      <option value=""></option>
      <cfloop from="#Year(Now())#" to="#getData.MinYear#" index="yearNum" step="-1">
         <option value="#yearNum#" <cfif yearNum eq URL.cDate>selected</cfif>>#yearNum#</option>
      </cfloop>
   </select>
</form>
</cfoutput>

Open in new window

0
 
_agx_Connect With a Mentor Commented:
> I keep getting the values #yearNum# for option tag values

usachrisk1983 just forgot to add <cfoutput> tags around the code.  That is why you are seeing the variables instead of them being evaluated.

But if you want at least 5 years to always be listed, you should take a look at my example above.  It will do that. Your current query will not.
0
 
jasch2244Author Commented:
agx_ thanks but I was looking more for what usachrisk1983: was doing ... I never wanted to go back just 4 years but all the way back to the earliest date. Good idea though
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.