Creating an RSS Feed using PHP, MySQL & Apache
October 29th, 2007 | Web Development
With the main clipart gallery now functional I took some time to add an RSS feed of my latest clipart uploads. Although I have plenty of previous experience with PHP and MySQL I’d never really given much thought to the structure or creation of an RSS feed. After playing around with a few examples and following a few tutorials I had something that nearly worked. The problem was the examples and tutorials I found all had bits that worked and bits that didn’t. After figuring out precisely how to make an RSS feed with PHP and MySQL I thought it worth noting down.
The following assumes you have previous experience with PHP and MySQL and just want a simple cut and paste example. Most of the code is self explanatory but I will add notes regarding specific issues I encountered.
The first thing to make clear is that an RSS feed is simply a dynamic web page in XML format with specific tags. If you have any experience coding dynamic web pages then you’ll easily be able to rework the code below to create your own RSS feed by following these steps:
1. Create a new file called rss.xml in a suitable directory on your website.
2. Create or edit an .htaccess file in this directory with the following command:
AddHandler application/x-httpd-php xml
This will allow your web server to parse the PHP in your rss.xml file.
3. Add the following code to your rss.xml file and edit as appropriate:
<?php
header('Content-type: text/xml');
echo '<?xml version="1.0" encoding="utf-8"?>';
echo '<rss version="2.0"
xmlns:content="http://purl.org/rss/1.0/modules/content/"
xmlns:wfw="http://wellformedweb.org/CommentAPI/"
xmlns:dc="http://purl.org/dc/elements/1.1/">';
echo '<channel>';
echo '<title>YOUR FEED TITLE</title>';
echo '<description>YOUR FEED DESCRIPTION</description>';
echo '<link>YOUR SITE URL</link>';
echo '<copyright>YOUR COPYRIGHT NOTICE</copyright>';
echo '<language>en</language>';
mysql_connect('YOUR_DATABASE_HOST', 'YOUR_DATABASE_USER', 'YOUR_DATABASE_PASSWORD');
@mysql_select_db('YOUR_DATABASE');
$query = ('SELECT your_id, your_title, your_description, UNIX_TIMESTAMP(your_date) AS your_date FROM your_table ORDER BY your_date DESC LIMIT 0,15');
$result = mysql_query($query);
while($row = mysql_fetch_array($result)) {
echo '<item>';
echo '<title>'.$row['your_title'].’</title>’;
echo ‘<description><![CDATA['.$row['your_description'].’]]></description>’;
echo ‘<link>ITEM_URL</link>’;
echo ‘<guid>UNIQUE_ID_(ITEM_URL)</guid>’;
echo ‘<pubDate>’.strftime( “%a, %d %b %Y %T GMT” , $row['your_date']).’</pubDate>’;
echo ‘</item>’;
}
mysql_close();
echo ‘</channel>’;
echo ‘</rss>’;
?>
Important things to remember:
- An RSS feed is limited to 15 items so ensure your query returns no more than 15 rows.
- Special characters need to be encoded with entity numbers not entity names.
- Wrapping a <![CDATA[ ]]> section around your description will allow you to include HTML markup in your item description. Use in other fields if needed.
- The date format requires a time zone which can be returned with the %Z parameter. I have hard coded GMT because occasionally BST will be returned by %Z and BST is not supported.
4. Upload your rss.xml file to your website and point your feed reader at its location. If everything went to plan then you should see your brand new RSS feed! If it doesn’t work or things look weird then use this Feed Validator to check for errors. Good luck!
Here is the RSS feed I created.
