How to query documents by year of publication -- when DocumentPublicationDate is formatted "yyyy-mm-dd 00:00:00.0"?

_agx_, thank you! I will try it out and get back to you. Eric

Okay, good. Sorry our posts crossed ;-)

_agx_,

Sorry to get back to you so late; it was a complicated evening. =)

The query to display the list of publication years is working great -- and, it makes sense to me. It is working here:

http://www.cep-dc.org/publications/

I'm confused about this part:

<!--- display the years --->
<cfoutput query="getDocumentYears">
<p><a href="index.cfm?selectedYear=#PublicationYear#">#PublicationYear#</a></p>
</cfoutput>

>>>Then use #url.selectedYear# on the index page query

Currently, the index.cfm file contains the output of query "getDocumentYears".

Shall I use another template -- like, displayPublication.cfm -- to display the result of query "getDocumentsForSelectedYear"?

Or -- as gdemaria likes to remind me -- can I re-use the code of index.cfm? =)

I will go ahead and set up a template, displayPublication.cfm, to display the result of query "getDocumentsForSelectedYear".

Then, in index.cfm, I will change my output code to this:

<!--- display the years --->
<cfoutput query="getDocumentYears">
<p><a href="displayPublication.cfm?selectedYear=#PublicationYear#">#PublicationYear#</a></p>
</cfoutput>

... but I am curious to know if I can use index.cfm to display the result of query "getDocumentsForSelectedYear".

Thanks again, hope your evening is going well.

Eric

Sorry I got busy this w/e and am just catching up :)

Shall I use another template ... or can I re-use the code of index.cfm?

Either's fine. It just depends on what you want.  You could easily put everything on one page.  I think all you'd need to do is set a default for #url.selectedYear#:

<cfparam name="url.selectedYear" default="#year(now())#">

Hey agx,  John doesn't want my help,  he's waiting for you...  

https://www.experts-exchange.com/questions/27023581/CF-problem-with-session-id.html?anchorAnswerId=35720434#a35720434

I think he's got a little crush on you ;)


Hi Eric - sorry to borrow you post..

Haha.  Maybe he's trying not to split points? Anyway .. I've got a meeting coming up in 30 ;-) Like I said, I'd take your advice over some of the others around here!

*LOL*

_agx_ it turned into a very busy weekend and morning. I am getting back to this task now and will let you know my results in a little while. Thanks, and hope your weekend was great.

~waves to gdemaria~

Eric

_agx_,

I've been looking at this for a while. I'm not sure how to get the output to work on my index.cfm page.

Right now I have this output, which lists document publication years -- per your last post. This works great:

<!--- display the years --->
<cfoutput query="getDocumentYears">
<p><a href="index.cfm?selectedYear=#PublicationYear#">#PublicationYear#</a></p>
</cfoutput>


But when I click on it, I get the same output .... a list of document publication years.

So,my query name="getDocumentYears" is being used, but my query name="getDocumentsandFiles" is being ignored in the output.

I have a feeling I am missing something very obvious. =)<br>

Eric

queries:

<!--- query to select document publication years --->
<cfquery name="getDocumentYears" datasource="#application.datasource#">
SELECT DISTINCT Year(DocumentPublicationDate) AS PublicationYear
FROM tbl_CEP_Documents
ORDER BY PublicationYear DESC
</cfquery>


<!--- query getDocumentsandFiles: this query selects columns from tables tbl_CEP_Documents D, tbl_Document_Has_Topic H, tbl_CEP_files F, and organize them by document publication year --->


<cfparam name="url.sortBy" default="DocumentPublicationDate">
<cfparam name="url.sortDir" default="desc">
<cfparam name="url.selectedYear" default="#year(now())#">

<cfquery name="getDocumentsandFiles" datasource="#application.datasource#">
SELECT DISTINCT D.DocumentID
  	 , D.DocumentTitle
     , D.DocumentType
     , D.DocumentAuthor
     , D.DocumentAbstract
     , D.DocumentKeyword
     , D.DocumentImage
     , D.DocumentPublicationDate
     , F.DocumentID 
     , F.FileID
     , F.FileName
     , F.FileLinkText
     , F.FileExtension
     , F.FileType
     , F.FileSize
     , F.FileLinkTextSortOrder
FROM tbl_CEP_Documents D
INNER JOIN tbl_Document_Has_Topic H
ON D.DocumentID = H.DocumentID

<cfif val(url.DocumentSubSubTopicID)>
AND H.DocumentSubSubTopicID = <cfqueryparam value="#url.DocumentSubSubTopicID#" cfsqltype="cf_sql_integer">

<cfelseif val(url.DocumentSubTopicID)>
AND H.DocumentSubTopicID = <cfqueryparam value="#url.DocumentSubTopicID#" cfsqltype="cf_sql_integer">

<cfelseif val(url.DocumentTopicID)>
AND H.DocumentTopicID = <cfqueryparam value="#url.DocumentTopicID#" cfsqltype="cf_sql_integer">
</cfif>
LEFT OUTER JOIN tbl_CEP_files F
ON D.DocumentID = F.DocumentID
AND year(D.DocumentPublicationDate) = <cfqueryparam value="#url.selectedYear#" cfsqltype="cf_sql_integer">

ORDER BY
<cfswitch expression="#url.sortBy#">
        <cfcase value="DocumentPublicationDate">D.DocumentPublicationDate</cfcase>
        <cfcase value="DocumentTitle">D.DocumentTitle</cfcase>
        <cfcase value="DocumentAuthor">D.DocumentAuthor</cfcase>
        <cfdefaultcase>D.DocumentPublicationDate</cfdefaultcase>
       </cfswitch>
       <cfif url.sortDir eq "DESC">
             DESC
       <cfelse>
             ASC
       </cfif>
        , D.DocumentID, F.FileLinkTextSortOrder ASC
</cfquery>

Select allOpen in new window

This is exactly the result that I want. =)

      <cfquery name="getDocumentYears">....</cfquery>
      <!--- display the years ---><cfoutput query="getDocumentYears">....</cfoutput>

      <cfquery name="getDocumentsandFiles">....</cfquery>
      <!--- display the documents---><cfoutput query="getDocumentsandFiles">....</cfoutput>

So users could just click on a link to switch years.  But maybe that's not what you're after? :)
              2011 | 2010 | 2009 | .....
              Documents for selected year
              ......document x .....
              ......document x .....
              ......document x .....
              ......document x .....

Both queries and outputs can reside on the same page -- that's what I was wondering.

So, I think I can set up a CFIF statement:

<CFIF val(selectedYear)>

<!--- display documents for a given year --->

<!--- begin output from query getDocumentsandFiles; list Document Title, Author, Abstract, Publication Date; and CEP Files associated with those documents; and download links to those files; within a given year selected by url.selectedYear --->
<cfoutput query="getDocumentsandFiles" group="DocumentTitle">


<!--- output group on DocumentID --->
<cfoutput group="DocumentID">


<!--- output Document Thumbnail Image on DocumentID --->
<cfif DocumentImage NEQ "">
<div class="float-left-img"><img src="/document_image.cfm?Attachment=#URLEncodedFormat(DocumentImage)#" class="border1" /></div>
</cfif>

<!--- output Document metadata on DocumentID --->
<h3>#DocumentTitle#</h3>
<div class="documentText">
<em>Author(s):</em> <span class="black">#DocumentAuthor#</span><br />
<em>Published:</em> <span class="black">#DateFormat(DocumentPublicationDate, "mmmm d, yyyy")#</span>
#DocumentAbstract#


<cfif getDocumentsandFiles.FileName NEQ "">
<h4>Download files:</h4>

<!--- output of query filename --->
 <cfoutput group="FileName">
 
<a href="/cfcontent_file.cfm?Attachment=#URLEncodedFormat(FileName)#">#FileLinkText#</a>
(#ucase(FileExtension)# format, #byteConvert(FileSize, "KB")#)

 <span class="hideme" class="nounderline"><a href="javascript:void(0)" class="nounderline">*</a></span>
 
              <span class="showme">Direct link:
              <a href="/cfcontent_file.cfm?Attachment=#URLEncodedFormat(FileName)#">http://www.cep-dc.org/cfcontent_file.cfm?Attachment=#URLEncodedFormat(FileName)#</a>
              </span>

<br />

<!--- /output of query filename --->
</cfoutput>
</cfif>

<!--- /output group on DocumentID --->
    </cfoutput>
   
<!--- /output from query getDocumentsandFiles --->
</cfoutput>

<CFELSE>

<!--- display the years --->
<cfoutput query="getDocumentYears">
<p><a href="index.cfm?selectedYear=#PublicationYear#">#PublicationYear#</a></p>
</cfoutput>

</CFIF>

I will try that and let you know what I get.

Eric

Either works, but the page seems a little empty when just displaying the years :)

I think you're right.

I think I will add the dropdown menu to the navigation bar above the main area of the page.

Working on this. More later this evening. E

_agx_,

I've got it basically working:

http://www.cep-dc.org/publications/index.cfm?selectedYear=2008

I'm going to do a little more testing tomorrow then close this question. The WHERE clause

WHERE  year(D.DocumentPublicationDate) = <cfqueryparam value="#url.selectedYear#" cfsqltype="cf_sql_integer">

and the year function that you suggested are working great. Thank you for your help! I'll wrap this up tomorrow.

Eric

Looks nice guys, good job.  

Eric, if someone clicks a year, when the page refreshes, why don't you highlight the selected year with a yellow background.. or something like that..   Would look nice and be clear to the user what they are seeing..

Just an idea..


(no points pls, seriously)

gdemaria, I had the same thought.

I am working on that. There are a couple of adjustments I need to make in this application ... but I am gonna open a new question if I cannot resolve those adjustments myself.

Thank you; hope you're well.

Eric

Thank as always to _agx_.