Creating an RSS feed with PHP and MySQL

What is RSS feed ?

RSS (most commonly expanded as Really Simple Syndication) is a family of web feed formats used to publish frequently updated works—such as blog entries, news headlines, audio, and video—in a standardized format. An RSS document (which is called a "feed", "web feed", or "channel") includes full or summarized text, plus metadata such as publishing dates and authorship. Web feeds benefit publishers by letting them syndicate content automatically. They benefit readers who want to subscribe to timely updates from favored websites or to aggregate feeds from many sites into one place. RSS feeds can be read using software called an "RSS reader", "feed reader", or "aggregator", which can be web-based, desktop-based, or mobile-device-based.
http://en.wikipedia.org/wiki/RSS_feed

RSS is a technology that is being used by millions of web users around the world to keep track of their favorite websites.
Using RSS feed we can share our website content with the rest of the Internet.

The RSS format is based on XML that is built using standardised tags. Following is an example of RSS document.

<rss version="2.0">
<channel>
<title>RSS Feed</title>
<description>RSS Feed</description>
<link>http://mywebsite.com</link>
<lastBuildDate>Wed, 24 Nov 2010 09:47:31 +0100</lastBuildDate>
<language>en-us</language>
<copyright>Copyright (C) 2010 mywebsite.com</copyright>

<item>
<title>Sample title one</title>
<link>http://mywebsite.com/news1.html</link>
<description>This is the description of the smaple title one</description>
<pubDate>Wed, 10 Nov 2010 11:04:12 +0100</pubDate>
</item>
<item>
<title>Sample title two</title>
<link>http://mywebsite.com/news2.html</link>
<description>This is the description of the smaple title two</description>
<pubDate>Wed, 10 Nov 2010 11:03:24 +0100</pubDate>
</item>
<item>
<title>Sample title three</title>
<link>http://mywebsite.com/news3.html</link>
<description>This is the description of the smaple title three</description>
<pubDate>Wed, 10 Nov 2010 11:01:53 +0100</pubDate>
</item>
</channel>
</rss>

We can generate an RSS feed similar as the above sample using PHP and MySQL.

In this example we are listing the latest articles from the database are feed. For that we need a database table . If not not created, we can create a table using the following code..

CREATE TABLE `articles` (
`id` int(11) unsigned NOT NULL auto_increment,
`title` varchar(255) NOT NULL default '',
`link` varchar(255) NOT NULL default '',
`introtext` mediumtext NOT NULL,
`created` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

RSS feeds are using XML tags. So we are going to generate the XML tags using PHP.

Step 1 : Creating the header

header("Content-Type: application/rss+xml; charset=ISO-8859-1");

The first step is that , we need to tell PHP that what type of data we would like to output. In the header function we are specify that the content type is " application/rss+xml ". This will output a XML formated data into the browser.

Step 2 : Setup a Database connecction

Create a database connection to fetch the datas from the database.

DEFINE ('DB_USER', 'dbusername');
DEFINE ('DB_PASSWORD', 'dbpassword');
DEFINE ('DB_HOST', 'dbroot');
DEFINE ('DB_NAME', 'dbname');

$connection = @mysql_connect(DB_HOST, DB_USER, DB_PASSWORD) or die('Could not connect to database');
mysql_select_db(DB_NAME) or die ('Error.. Could not connecct to database');
$query = "SELECT * FROM articles ORDER BY id DESC";
$result = mysql_query($query) or die ("Error.. Could not execute query");

Step 3 : Add RSS Feed Information

Next add the information about our RSS feed such as the title of it, the description, and a link to the the site.
Also add the xml version, rss version etc.

$rssfeed = '<?xml version="1.0" encoding="UTF-8"?>';
$rssfeed .= '<rss version="2.0">';
$rssfeed .= '<channel>';
$rssfeed .= '<title>RSS Feed</title>';
$rssfeed .= '<description>RSS Feed</description>';
$rssfeed .= '<link>http://mywebsite.com</link>';
$rssfeed .= '<lastBuildDate>Wed, 24 Nov 2010 09:47:31 +0100</lastBuildDate>';
$rssfeed .= '<language>en-us</language>';
$rssfeed .= '<copyright>Copyright (C) 2010 mywebsite.com</copyright>';

Step 4 : Adding RSS Items

Next, we need to extract our data by looping through our MySQL database to create the <item> tags.

while($row = mysql_fetch_array($result))
{
extract($row);
$rssfeed .= '<item>';
$rssfeed .= '<title>' . $title . '</title>';
$rssfeed .= '<link>http://mywebsite.com/news'.$id.'.html</link>';
$rssfeed .= '<description>[CDATA[' .$introtext . ']]</description>';
$rssfeed .= '<pubDate>' . $created . '</pubDate>';
$rssfeed .= '</item>';
}

Make sure you escape any characters that might cause your XML to invalidate, these characters include <, >, & - I like to enclose any content that may contain HTML inside a CDATA section.

Step 5: Close Channel, RSS tags and output the rssfeed.

$rssfeed .= '</channel>';
$rssfeed .= '</rss>';

echo $rssfeed;

Step 6: Validate your feed

Validate your feed using FeedValidator.org.


Full Source Code:

<?php
header("Content-Type: application/rss+xml; charset=ISO-8859-1");
DEFINE ('DB_USER', 'root');
DEFINE ('DB_PASSWORD', '');
DEFINE ('DB_HOST', 'localhost');
DEFINE ('DB_NAME', 'fourjmktg9908_iapc');
$connection = @mysql_connect(DB_HOST, DB_USER, DB_PASSWORD) or die('Could not connect to database');
mysql_select_db(DB_NAME) or die ('Error.. Could not connecct to database');
$query = "SELECT * FROM jos_content ORDER BY id DESC";
$result = mysql_query($query) or die ("Error.. Could not execute query");

$rssfeed = '<?xml version="1.0" encoding="UTF-8"?>';
$rssfeed .= '<rss version="2.0">';
$rssfeed .= '<channel>';
$rssfeed .= '<title>RSS Feed</title>';
$rssfeed .= '<description>RSS Feed</description>';
$rssfeed .= '<link>http://mywebsite.com</link>';
$rssfeed .= '<lastBuildDate>Wed, 24 Nov 2010 09:47:31 +0100</lastBuildDate>';
$rssfeed .= '<language>en-us</language>';
$rssfeed .= '<copyright>Copyright (C) 2010 mywebsite.com</copyright>';
while($row = mysql_fetch_array($result)) {
extract($row);
$rssfeed .= '<item>';
$rssfeed .= '<title>' . $title . '</title>';
$rssfeed .= '<link>http://mywebsite.com/news'.$id.'.html</link>';
$rssfeed .= '<description>[CDATA[' .$introtext . ']]</description>';
$rssfeed .= '<pubDate>' . $created . '</pubDate>';
$rssfeed .= '</item>';
}
$rssfeed .= '</channel>';
$rssfeed .= '</rss>';
echo $rssfeed;
?>

Save this file as index.php and save it in the folder named 'feed'. Now we can directly access the rss feeds from the browser using the link ' http://mywebsite.com/feed '