Format RSS feed to display by category

Discussion in 'XML & RSS' started by klarkgriswold, May 18, 2009.

  1. #1
    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...

    
    <!---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--->
    
    Code (markup):
    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!!
     
    klarkgriswold, May 18, 2009 IP