[X] My Assistant

[midasxl]

Hello all and thanks again for your time. I have a snippet of code that succesfully queries a database and uses the records to generate xml data for RSS. Dig it...

CODE

<!---Query the database--->
<cfquery name="feed" datasource="public_www">
SELECT *
FROM rss_current_news
WHERE active_flag = 1
</cfquery>

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

<cfprocessingdirective suppresswhitespace="yes
<cfxml variable="xmlobject" casesensitive="yes">

<rss version="2.0">
<channel>
<title>NCDOC RSS Feed</title>
<link>rss/feed/rssFeed.xml</link>
<description>RSS Feed for Current 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--->

As you can see the cfloop will walk through the database findings and create an RSS item for each record. What I would like to do is create the RSS in such a way that it groups the items by Title. I have several records that share the same title and news information under each one.

For example, I have a title (category) called Alerts, and a title(category) called News. Within each category there are several stories. I want the RSS to display them like this...

Alerts
story 1
story 2
story 3
story 4
News
story 1
story 2
story 3

etc.

Right now they look like this...

Alerts
story1
Alerts
story 2
Alerts
story 3
Alerts
story 4
News
story 1
News
story 2
News
story 3
etc.

The database has 7 columns: rss_id, title, subject, url, description, date, and active flag.

I need a way to query the database and categorize by title to achieve the above results. I am not sure how to approach this however. Any suggestion will be greatly appreciated, thank you!!

[midasxl]

OK, so here is what I came up with...

CODE

<!---Query the database to get category data--->
<cfquery name="category" datasource="rss_test">
SELECT categoryID, name, active
FROM rss_category
WHERE active = 1
ORDER BY name ASC
</cfquery>

<!---Query the database to get article information--->
<cfquery name="feed" datasource="rss_test">
SELECT *
FROM rss_news
</cfquery>

<cfprocessingdirective suppresswhitespace="yes">
<cfxml variable="xmlobject" casesensitive="yes">

<rss version="2.0">
<channel>
<cf:treatAs xmlns:cf="http://www.microsoft.com/schemas/rss/core/2005">list</cf:treatAs>
<title>NCDOC RSS Feed</title>
<link>rss/feed/rssFeed.xml</link>
<description>RSS Feed for Current NCDOC News </description>
<lastbuilddate>Date and Time of Last Change</lastbuilddate>
<language>en-us</language>
<copyright>© 2009 NCDOC</copyright>
<ttl>10</ttl>
<image>
<title>NCDOC RSS Feed</title>
<link>rss/feed/rssFeed.xml</link>
<url>images/rss_icon.jpg</url>
<width>144</width>
<height>33</height>
<description>RSS Feed for Current NCDOC News</description>
</image>


<cfoutput>
    
<!--- Outer Loop to handle categories--->
<cfloop query="category">
    <item>
        <title>#XmlFormat(category.name)#</title>
    </item>
        <!--- Inner Loop to handle articles--->
            <cfloop query="feed">
            <cfif category.name IS feed.title>
            <item>
                <link>#XmlFormat(feed.url)#</link>
                <description>#XmlFormat(feed.subject)#</description>
                <pubDate>#XmlFormat(feed.add_date)#</pubDate>
                <category>#XmlFormat(category.name)#</category>
            </item>
            </cfif>
            </cfloop>
</cfloop>

</cfoutput>
</channel>
</rss>

</cfxml>
</cfprocessingdirective>

<cfoutput>#xmlobject#</cfoutput>

This does what I need it to do but only for the benefit of viewing the feed in the browser (ie7 in my world). My RSS aggregators do not like the way this writes the xml. This was expected, so I don't think this is a viable option but it was interesting to figure out.

Thanks!