RSS feed from database query

Cannot get the date to show in feed

Page 1 of 1

6 Replies - 3586 Views - Last Post: 02 October 2009 - 09:13 AM Rate Topic: -----

#1 midasxl  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 191
  • Joined: 03-December 08

RSS feed from database query

Post icon  Posted 27 July 2009 - 06:22 AM

Thanks in advance for your time and assistance! I am building an RSS feed from a database query as show in the code below. As you can see in the loop that builds the rss nodes I am trying to include the date, but it does not show up in the resulting feed. Anyone see what I may be doing wrong? Is <date> a legitimate node? Thanks again!

<!---Query the database--->
<cfquery name="feed" datasource="my_source">
SELECT *
FROM rss_current_news
WHERE active_flag = 1
ORDER BY mod_datetime DESC
</cfquery>

<!---***********************************begin .xml generation**********************************************--->

<!---Remove white space from generated content in the tag body--->
<cfprocessingdirective suppresswhitespace="yes"><!---Prevents ColdFusion from putting white space characters in front of the XML declaration--->
<!---Create a CF XML document object that contains the markup--->
<cfxml variable="xmlobject" casesensitive="yes">

<rss version="2.0">
<channel>
<title>NCDOC RSS Feed</title>
<link>rss/feed/</link>
<description>RSS Feed for Current NCDOC News </description>
<lastbuilddate>Date and Time of Last Change</lastbuilddate>
<language>en-us</language>
	<cfoutput>
			   
	  <cfloop query="feed">
	  
			  <item>
				<title>#XmlFormat(feed.title)#</title>
				<description>#XmlFormat(feed.caption)#</description>
				<date>#XmlFormat(feed.mod_datetime)#</date>
				<link>#XmlFormat(feed.link)#</link>
			</item>
	  
	   </cfloop>
	   
	 </cfoutput>
</channel>
</rss>

</cfxml>
</cfprocessingdirective>

<cfoutput>#xmlobject#</cfoutput>

<!---***********************************end .xml generation**********************************************--->



Is This A Good Question/Topic? 0
  • +

Replies To: RSS feed from database query

#2 midasxl  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 191
  • Joined: 03-December 08

Re: RSS feed from database query

Posted 27 July 2009 - 07:32 AM

OK, I figured out I need to use <pubDate> instead of just <date>. But now I have another question. My most recent feed item reads "Today, July 27, 2009, 6 Hours Ago". Why does it say 6 hours ago? I just put it up?? Thanks!
Was This Post Helpful? 0
  • +
  • -

#3 xheartonfire43x  Icon User is offline

  • D.I.C Regular

Reputation: 46
  • View blog
  • Posts: 454
  • Joined: 22-December 08

Re: RSS feed from database query

Posted 28 July 2009 - 11:38 AM

Can you post the RSS code that your web browser receives?

This post has been edited by xheartonfire43x: 28 July 2009 - 12:10 PM

Was This Post Helpful? 0
  • +
  • -

#4 midasxl  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 191
  • Joined: 03-December 08

Re: RSS feed from database query

Posted 03 August 2009 - 05:17 AM

xheartonfire43x,
Here is the code from the browser. I have one item in the feed for simplicity. I added the item at 08:13 today, and the feed reads "added 4 hours ago." Thanks for your assistance.

<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0">
<channel>
<title>RSS Feed</title>
<link>rss/feed/</link>
<description>RSS Feed for Current News </description>
<lastbuilddate>Date and Time of Last Change</lastbuilddate>
<language>en-us</language>
	
	  
<item>
				<title>Test</title>
				<description>Test</description>
				<pubDate>2009-08-03 08:13:04.0</pubDate>
				<link/>
</item> 
	   
	  
				 
	   
</channel>
</rss> 


Was This Post Helpful? 0
  • +
  • -

#5 xheartonfire43x  Icon User is offline

  • D.I.C Regular

Reputation: 46
  • View blog
  • Posts: 454
  • Joined: 22-December 08

Re: RSS feed from database query

Posted 04 August 2009 - 11:14 AM

View Postmidasxl, on 3 Aug, 2009 - 04:17 AM, said:

xheartonfire43x,
Here is the code from the browser. I have one item in the feed for simplicity. I added the item at 08:13 today, and the feed reads "added 4 hours ago." Thanks for your assistance.

<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0">
<channel>
<title>RSS Feed</title>
<link>rss/feed/</link>
<description>RSS Feed for Current News </description>
<lastbuilddate>Date and Time of Last Change</lastbuilddate>
<language>en-us</language>
	
	  
<item>
				<title>Test</title>
				<description>Test</description>
				<pubDate>2009-08-03 08:13:04.0</pubDate>
				<link/>
</item> 
	   
	  
				 
	   
</channel>
</rss> 


Could it be that your local machine & your coldfusion server are on different timezone settings?
Was This Post Helpful? 0
  • +
  • -

#6 midasxl  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 191
  • Joined: 03-December 08

Re: RSS feed from database query

Posted 30 September 2009 - 05:33 AM

Ok, better late than never. I have revisited this one and have come up with the following...

<cfset todayDate=#feed.add_date#>
<cfset thisDate="#DateFormat(todayDate, 'ddd, dd mmm yyyy')# #TimeFormat(todayDate, 'hh:mm:ss')# EDT">
<item>
<title>#XmlFormat(feed.title)#</title>
<description>#XmlFormat(feed.caption)#</description>
<pubDate>#thisDate#</pubDate>
<link>#XmlFormat(feed.URL)#</link>
</item>



I specified the time and date format. Apparently if you do not specify the time zone (in this case EDT) it defaults to GMT.
Was This Post Helpful? 0
  • +
  • -

#7 CR250  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 58
  • Joined: 14-June 08

Re: RSS feed from database query

Posted 02 October 2009 - 09:13 AM

Just in case you didn't know and for anyone that stumbles on this thread you can create RSS feeds with CF8 here is an excellent example:

http://tutorial27.learncf.com/
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1