Dynamic RSS feeds using PHP, MySQL and Apache

UPDATE: I wrote this code a while ago, and haven’t really updated it since. There may be some a bug within. Depending on your set up, this tutorial may not even work for you. If you’re a copy-and-paster (and can’t debug PHP and MySQL scripts), you may not want to use this tutorial. There are some helpful bits in the comments. Please read them before contacting me.

I will update when I have the time and energy to go through it properly.

PHP and MySQL can be used to create dynamically-generated HTML pages. But with a little more know-how, you can also use this combo with Apache to create a dynamically-updated RSS feed.

Sure, most blogging software packages generate their own feeds. But why use them, when creating your own is so much geekier?

What you’ll need

I’m assuming you’re already using a PHP & MySQL-based publishing system, and that you’ve got database full of content.

You’ll also need the ability to use .htaccess files (or have access to your server’s configuration file).

RSS is pretty simple to pick up. The code in this article will give you a basic feed. If you’d like a more complex feed, consult the RSS 2.0 specification.

Step 1: Create the .htaccess file

RSS files, like all XML files, are static text. To make our feed dynamic, we’re going to tell our server to treat files with an .xml extension as PHP files.

To do that, simply create a file named .htaccess. Add AddType application/x-httpd-php .xml and save it to your server. If you have access to your server’s configuration (httpd.conf) file, you can put the AddType declaration there instead.

Keep in mind that this will cause all files with an .xml extension in that directory (or on your server) to be parsed as PHP.

Step 2: Set the header

By default, PHP sends text with an HTML content type. As a result, your aggregator may not recognize it as a valid RSS / XML file.

Enter the header() function. We’ll put the following header near the top of our script, before any text gets sent to the client.

<? header('Content-type: text/xml'); ?>

Step 3: A basicBegin the feed

A basic RSS document consists of a channel. Within each channel are items. At minimum, each item requires a title, description and link. In this example, we’re also going to add a publication date.

Your feed should start off like this:

<rss version="2.0">
<channel>
<title>Name of your site</title>
<description>A description of your site</description>
<link>http://yoururl.com/</link>
<copyright>Your copyright information</copyright>

Step 4: Retrieve articles from the database

For this example, let’s say we’ve got a table named ‘articles’ with four fields: id, title, body and pubDate (for ‘date published’).

We’ll need a title, description and publication date for each item. We’ll also need each item’s id to create the page link. Our SQL query might look like this (assumes the database host name, user name and such are already available I’m going to assume that you have already established a link to your database):

<?
$q="SELECT id,title,body,UNIX_TIMESTAMP(pubDate) AS pubDate
FROM articles LIMIT 0,15 ORDER BY pubDate DESC";
$doGet=mysql_query($q);
?>

Let’s examine the query. We’re selecting the id, title and body for most recent eight fifteen entries. That’s the maximum number of items that can be included in a valid RSS feed.

We’re also telling MySQL to retrieve the pubDate field as a UNIX time stamp, and save it in an array (or an you can save it as an object if you prefer). We’ll need a UNIX-style time stamp to generate a publication date.

Step 4: Build the feed Add your items

Now we get to the good stuff: Building the actual feed. We’ll take the code above, and add the following lines.

<?

while($item=mysql_fetch_array($doGet))
 {
  $id=$doGet['id'];
  $title=strip_tags($doGet['title']);
  $body=strip_tags($doGet['body']);
  $body=substr($doGet['body'],0,150);
  $=strftime("%a, %d %b %Y %T %Z",$doGet['']);
 }

?>


<?
while($result = mysql_fetch_array($doGet)){
?>
     <item>
        <title> <?=htmlentities(strip_tags($result['title'])); ?></title>
        <description> <?=htmlentities(strip_tags($result['body'],'ENT_QUOTES'));?></description>
        <link>http://yoururl.com/pathtostory/and_page.php?p=<?=$result['id'];?></link>
        <pubDate> <?=strftime( "%a, %d %b %Y %T %Z" , $result['pubDate']); ?></pubDate>
     </item>  
<? } ?>  

The code above does the following:

1. Stores the query results as an array (using mysql_fetch_array())
2. Escapes HTML entities in the title and body (using htmlentities())
3. Strips the HTML tags out of title and body (using strip_tags())
4. Generates a valid date stamp (using strftime())

That code above is essentially the RSS feed. Using a while loop, we’re making the script output an RSS item for each row returned from the database. We’re also using strip_tags() and htmlentities() to strip HTML from the title and body, and to escape ampersands and quotes.

All that’s left is to add the closing tags for the channel and document.

Step 4: Build the feed

Now we get to the good stuff: Building the actual feed. We’ll take the code above, and add the following lines.

<?
while($item=mysql_fetch_array($doGet)
 {
  $id=$doGet['id'];
  $title=strip_tags($doGet['title']);
  $body=strip_tags($doGet['body']);
  $body=substr($doGet['body'],0,150));
  $=strftime("%a, %d %b %Y %T %Z",$doGet['']);

  // output to client

?>

  <item>
  <title><?print htmlentities($title,’ENT_QUOTES’);?></title>
  <description><?print htmlentities($body,’ENT_QUOTES’);?></description>
  <link>http://your_url/path/and_page.php?article=<?print $id;?></link>
  <><?print $;?></>
  </item>

<? } ?>

</channel>
</rss>

Because RSS feeds can’t contain special characters, you’ll want to convert apostrophes, quotes and similar characters to their respective entities using htmlentities(). Then you’ll need to add the closing tags for the channel and document.

From beginning-to-end, your script should look a bit like this:

<? header('Content-type: text/xml'); ?>

<rss version="2.0">
<channel>
<title>Name of your site</title>
<description>A description of your site</description>
<link>http://your_home_page_url/</link>
<copyright>Your copyright information</copyright>

<?
$q="SELECT id,title,body,UNIX_TIMESTAMP(pubDate) AS pubDate
FROM articles LIMIT 0,15 ORDER BY pubDate DESC";
$doGet=mysql_query($q);

while($result = mysql_fetch_array($doGet)){
?>
     <item>
        <title> <?=htmlentities(strip_tags($result['title'])); ?></title>
        <description> <?=htmlentities(strip_tags($result['body'],'ENT_QUOTES'));?></description>
        <link>http://yoururl.com/pathtostory/and_page.php?p=<?=$result['id'];?></link>
        <pubDate> <?=strftime( "%a, %d %b %Y %T %Z" , $result['pubDate']); ?></pubDate>
     </item>  
<? } ?>  

</channel>
</rss>

By the way, if you wish to have cleaner URIs (instead of filename.php?article=1), read Creating clean URIs with PHP.

Step 5: Validate and announce

Before you make your feed “live,” don’t forget to check it with a validator. An invalid feed can cause problems with aggregators and browsers. There are several web-based validation tools such as Feed Validator that make validation a snap.

UPDATE: You can also let aggregators find your feed automatically.