Solved

Help with a date query

Posted on 2009-05-04
8
145 Views
Last Modified: 2013-12-24
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

0
Comment
Question by:jasch2244
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 13

Accepted Solution

by:
usachrisk1983 earned 250 total points
ID: 24301344
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
 
LVL 1

Author Comment

by:jasch2244
ID: 24301354
I'm using MySQL will it make a differnece? I'm still a newb
0
 
LVL 13

Expert Comment

by:usachrisk1983
ID: 24301369
The MIN and YEAR functions are supported by MySQL, so it should work fine.
0
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
LVL 52

Expert Comment

by:_agx_
ID: 24301494
>  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
 
LVL 1

Author Comment

by:jasch2244
ID: 24301544
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
 
LVL 52

Expert Comment

by:_agx_
ID: 24301553
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
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 250 total points
ID: 24301562
> 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
 
LVL 1

Author Closing Comment

by:jasch2244
ID: 31577863
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

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
how can I backup my incomplete website from hostgator? 3 73
IIS Site Configuration Copy 2 87
ip / url redirect 13 74
Link failure 16 89
Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Suggested Courses

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question