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:
- Stores the query results as an array (using
mysql_fetch_array()) - Escapes HTML entities in the title and body (using
htmlentities()) - Strips the HTML tags out of title and body (using
strip_tags()) - 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.
Now we get to the good stuff: Building the actual feed. We’ll take the code above, and add the following lines. Because RSS feeds can’t contain special characters, you’ll want to convert apostrophes, quotes and similar characters to their respective entities using
Step 4: Build the feed
<?
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>
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.
Awesome article! Thanks heaps =)
This is the error I’m getting in my logs…
“PHP Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource”
It seems to have a problem with this line…
“while($result = mysql_fetch_array($doGet)){”
Thoughts?
Steph, there are two things that could be happening:
1. There’s a typo and you need to change the name of $doGet to match the name of the variable you used in mysql_query().
2. There’s an error in your SQL code. I’m too lazy to scroll up, but someone mentioned that the SQL syntax won’t work with MySQL 4.x or higher. You have to put the LIMIT 0,15 at the end… or something like that.
WoW! I could find what i expected. I am doing a site on windows tutorial, interactive guide. I needed to use an automated feed created. This code really helped me a lot.
Thank!!
Thanx!!
Thanx a lot!!!
Thnaks for providing such awesome tutorial.
It realy helped me.
But now i have a query regarding dynamic links in Feeds.
When I wrote http://yoururl.com/pathtostory/and_page.php?p=
inside tags that works fine but when i change it
to (Like that) http://yoururl.com/pathtostory/and_page.php?p=&a= than it displays errors.
Thnx in Advance
Brooke
thanks.. for the Code
Well wrote! This is honestly the best tutorial I have read for taking advantage of RSS feeds when you loop results in a database.
I do have a different method for looping the results, but that was a great idea of re-writing page extensions for PHP to .xml. I had no idea that would even work. Nice find.
Hi, worked a treat – thanks!
Now all I need is a “real-time” RSS reader (polls every 10 secs) for my smartphone. Have you seen anything? Or can you think of another way to get the data to the phone quickly and economically?
i think some phones have RSS readers available. nokia’s smartphones come to mind.
another method might be to create a fetching program on a server and have it push to your phone. if you have a web-enabled phone, you can just build a web page with that data.
Wow…this is great. Just great! Thanks
I’ve a problem.
My site give the error that he can’t refresh the rss feed and he also sais that he will try it again after a while. But he don’t show any.
here you can see the page: http://www.polderbad.nl/rss/index.php
Maybe anyone can help me.
Sorry, I gave a wrong link. The correct link is: http://www.polderbad.nl/nieuwemap/index.php
Hi Erik, check (or remove) your .htaccess file. See if that works.
Hi!
Great code!
But I get errors when trying to validate!
It says it is not UTF8 compatible characters or something like that.
I use iso-8859-1 format… where and how do I add/change that to make it work?
Sincerely,
Andreas
Andreas, you’ve asked a question that is over my head
, but I’ll take a stab at answering.
I *think* all XML files have to be UTF-8 or UTF-16. Usually you can specify your character set with your text editor.
If you have access to your php.ini file, you can also change your default character set there. See the PHP web site for details: http://us2.php.net/manual/en/configuration.changes.php
Hi again Tiffany!
Thank you for you help!
Well, I saved the file in UTF-8 format and then it worked great. But now my issue seems to be this. When fetching from the database, it seems like PHP is using ISO-8859-1 format on the characters (swedish last three letters), which makes makes the Swedish letters not seen. They become a vertical line only.
Do you know if it is possible to re-format PHP output somehow?
Thank you again for taking your time to reply!
Sincerely,
Andreas
Your database may be storing it in ISO-8859-1. I’m not sure if there’s an easy fix that won’t cause some data loss.
You can modify your configuration settings in PHP, but you’ll still run into encoding problems if your database is using a different character set.
@Andreas: Look into PHP’s multi-byte string and iconv functions. Those will help you switch between character sets.
I’ve tried and tried and tried, but the RSS Feed still won’t work for my blog – I made my blog using Codegrrl.com’s blog tutorial. What is it that doesn’t make up between both tutorials? I really don’t understand why it won’t work.
Thankyou, thankyou, thankyou!!! I searched far and wide for a good tutorial/forum talking about creating an RSS feed using php/mysql. I couldn’t for the life of me find out why my code wasn’t working and after coming to your site I finally figured out that the .htaccess file was the culprit. I had previously installed wordpress and it wrote script to that file, which prevented me from writing my own RSS feed. After emptying the wordpress code from the file and trying the method of putting the index.php file in the feed folder, it worked! I am so ecstatic that if I could do cartwheels, I would do them!!
Too bad the author doesn’t provide feedback for this; worthless. :/
Hi, your rss 2.0 specification is broken. The correct one appears to be http://cyber.law.harvard.edu/rss/rss.html
Thanks for this awesome tutorial! Perfect in every way plus the only one I found like it. Keep up the good writin’!
To ‘Bill Bull’ above. This isnt worthless. I got it up and running in 5 minutes flat. And to expect tiffany to reply to everyones emails for support is ridiculous. The hard work has been done for you.
Thankyou tiffany, excellent tutorial, worked perfectly after I changed the Limit clause to the end of the SQL statement. Much appreciated.
I’m desperately need this script for mya blog (i create myself).
Thank you so much, tiff
Great simple 123 done tutorial. I will combine the knowledge you have shared with us and I will follow the exact specification pointed out by anon.
(“SELECT id,tittle,short_s,rank FROM news WHERE place=\”in\” AND pos=\”bar\” ORDER BY rank DESC “);
get some error
Do anyone else realizes that nowadays the web is becoming a simpler universe? years ago to do anything you have to read read code code and study and burn eyebrows a lot; but now, technology is simplifying itself to the point where things are already done and you just call them (oop) and mixing ingredients sorts out the most exquisit electronic dishes. awesome!!!
Thank you!!!
I was searching this for a looooong time, finally!
Thanks allot for the script!
how to connect to database??
I was trying with your code, and my problem is…
In firefox..
RSS page is cumn.. but it doesn’t show any content.. only the heading is cumming…!!! what could be the wrong.. I edited the query according to my table structure…
in IE 7
Internet Explorer cannot display this feed
This feed contains code errors.
what could be the problem??
my query is:SELECT mesgID,title,content,UNIX_TIMESTAMP(date) AS pubDate
FROM mesgboard ORDER BY date DESC LIMIT 0, 5;
@bipi: Research before you ask
http://www.php.net/mysql_connect"
Great tut, will be implementing soon!
Hi, excellent stuff, I’ve been wanting to do this for a while, thank you for your code.
I’m in the process of designing a complex database driven website and was wondering if there is a way to get data from a "latest news" table and also from a "weekly tv listings" table.
The idea being that when news is updated on the site, the RSS shows the update, but also the RSS would hopefully show the latest TV listings for programs in the tv listings table as and when they are updated.
There are different fields in each table and the structure of each is different.
Can this be achieved? The field names are different in each table and there are more fields in one than the other so I’m assuming there’d be some kind of PHP statement in there that gets the latest posts from either of the tables?
Please help!
Many thanks
Jonathan
Hi,
I’m looking for a PHP programer to develop and RSS2.0 feed for my site.
Anyone interested in doing it?
This is a paid job.
Regards,
Jose
This is a very good method and I’ll be using it for a gallery website that is compatible with Piclens.
The XML will be automatically generated when a new image is submitted and published after being checked.
Nice tutorial. Do you mind if i add this to my tutorial site? (www.tutorialwave.com)
Hi,
First of all thanks for the code.
I very good understandable even for a Dutchman.
I however, get the errer that it isn’t allowed to place a space after
The error appears on the first line between and header
Can you plse help me?
Kind regards,
Martin Smits
[...] har endelig klart å skrape sammen en feed basert på det her: Dynamic RSS feeds using PHP, MySQL and Apache • Tiffany B. Brown Har endret: $q="SELECT id,title,body,UNIX_TIMESTAMP(pubDate) AS pubDate FROM articles LIMIT [...]
PHP code will be displayed if htaccess fails, so for extra security i recommend to:
*use SQL login with read rights only
*put DB connection code in another php file (DB.php) and include it to rss.xml via include(‘DB.php’), only ‘include(‘DB.php’)’ will be displayed protecting your login.
it's impossible för me to add the result as an google gadget, "Error parsing module spec: Not a properly formatted file missing xml header. " i think what they are asking for is <xml version="1.0">, but it seems impossible to this otherwise gread script…
thanks
i found this very helpful.
Great tutorial..thank you!
This tutorial is absolutely amazing, thank you so much for sharing – I've been looking for something like this a long time.
possibly the simplest way !! thanks !!
Thanks a lot
this was helpful
http://blogs.law.harvard.edu/tech/rss/
The link to that site doesn't work =