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.
Brilliant. I’ve been looking for something like this for ages! Clear explanation and simple code that works.
Thank you.
[...] Dynamic RSS feeds using PHP, MySQL and Apache :: tiffany b. brown // v 4.1 (tags: rss php MySQL apache webdev) [...]
there is an error in while($result = mysql_fetch_array($doGet)){
would you be helpful and point out where the error is ali?the last block of code is fine, although the first block did have the wrong variable name.Something in my code is not validating. Can you help? (http://www2.science.tamu.edu/news.xml)
Seems like the first line in most RSS feeds is something like:
Do you think this is the problem? Or is this covered by that one header line:
Any help is appreciated.
Hey Kendra, it looks like your code didn’t come through.
What error are you getting when you try to validate it?
Wow, you’re my total hero.
Thanks so very much for this quick and
Wow, you?¢‚Ǩ‚Ñ¢re my total hero.
Thanks so very much for this quick and EASY tutorial. Worked a treat, and had me up and running in no time.
Going to use this to submit my site to http://gnoos.com.au.
I’m so ultra-stoked at home easy this was.
cheers!
PS - I didn’t need the (very clever) .htaccess trick. I simply created a new directory under the docroot called “feed” and created the script I adapted from your methods as “index.php”. Thus: The feed url is http://brad.globeproductions.com.au/feed
Just a tip!
Hey, thanks so much for your help, but somehow I’m still missing something…I’m coding this in DreamWeaver and my site is hosted on godaddy.com…the RSS feed, as you will soon see, is tailored for iTunes…My code is below…could you please take a look at it and email me back?? I’d really apprecaite it…
[code]
Emerge Worship
http://www.emergeworship.com/Podcasts/
Emerge, a college and young adult worship service where people from different backgrounds and churches throughout the Dallas area can come together and worship. http://www.emergeworship.com
en-us
Emerge Worship
length= type=”/>
no
Emerge Worship
dsartain@emergeworship.com
[/code]
well that didn’t work at all, did it! If you could please email me I’ll just send you the file…if you have time…
Sorry Don, I can’t provide technical support. I don’t know the particulars of your set up., so I can’t be of much help.
There is an error for some ppl. becos of ur MySQL version.
Instead of
$q="SELECT id,title,body,UNIX_TIMESTAMP(pubDate) AS pubDate
FROM articles LIMIT 0,15 ORDER BY pubDate DESC";
The Limit clause should be normally at the end of the SQL statement:
;o)
$q="SELECT id,title,body,UNIX_TIMESTAMP(pubDate) AS pubDate
FROM articles ORDER BY pubDate DESC LIMIT 0,15";
Thanks Anan. This tutorial used MySQL 3.2 or something … I’ll get around to updating it soon.
$query=”SELECT s.speaker, r.cast_id, r.title, r.filename, r.description, r.pubDate, r.size FROM `speakers` as s INNER JOIN `RSS_info` as r ON s.speaker_id=r.speaker_id;”
Is my query statement…I’m running MySQL 5.0…or something like that…I don’t see where that would be an issue though…all I get when I test it and view the source are open and close HTML tags, with nothing in the middle…
I figured out what it was, thanks so much!
Thanks for the awesome code, I’ve used it before and it’s perfect.
I’ve tried doing it again on a different setup, and I’m having issues. Basically I’m getting parsing errors as it is not recognising the PHP tags. I have put the .htaccess file up (which is the exact same as the one I used to use!), and it doesn’t seem to be recognising it.
Is there any instances where the .htaccess file wouldn’t work, or perhaps overridden globally?
thanks!
ps, the feed is here:
http://s05.webdev.stir.ac.uk/rss/rss.xml
[...] The showcase is image rich, which is always a bonus in the rare book trade, although it seems that recent arrivals are yet to be photographed. The only thing lacking from?Ǭ†the site is an RSS feed for the recent arrivals section.?Ǭ†For developers at Royal Books (and anywhere else for that matter) creating a feed is relatively easy. Have a look at this site for some help, and this one for a bit of code to help you construct your feed dynamically. [...]
Awesome code!thanks
OK checare este para ver que tal funciona. Gracias
Mexman
Thanks for the code. It is very helpful.
The question: Why use RSS feeds?
I stumbled across your article in my efforts to build a dynamic web page for local news. As I was explaining my findings, the question came up “Why RSS feeds?” Why not just pull the information dynamically to my PHP page and scroll through the headlines using java scripting?
I excited with what I’ve been able to accomplish with RSS feeds from national sites like ABC but isn’t this a bit rich for local news headlines?
Thanks for your contribution and input!
Simply put: RSS puts the reader in control. RSS feeds can be used to create an aggregator page, or on a customized homepage. If you want to share headlines across a network of sites, RSS can do that.
If you want to create a branded widget for Yahoo! Widget Engine or Mac OS X Dashboard, you can use RSS to serve those headlines.
Yes, you could use JavaScript, but then what can your user do with it in that form? They still have to remember to visit your web site in order to see your headlines. With RSS, they subscribe once and everything goes to their aggregator.
It’s really a matter of what you’d like people to receive your data: make them come to you, or deliver it right to them.
can you please help whenever i try the feed and look at the code it says there that Supplied argument is not a valid MySQL result resource, and he points to a line where there is
while($result = mysql_fetch_array( $do )) {
what’s rong here can someone please help me
Supplied argument is not a valid MySQL result resourcethis error means that the variable you’re using in mysql_fetch_array() is not the variable name in which your mysql_query is being stored.
Cindreta, I am guessing that the error you are seeing means that your SQL statement is not working, so the variable ‘$do’ has the value false, instead of being a valid MySQL result set.
Check that you put the SQL in correctly, and check that your database is set up correctly. Test the SQL with your database but using a MySQL client instead of via PHP - that will tell you whether the problem is with the PHP code, or with the database and SQL.
Good luck!
HEY!
Hope you’re all well
I’m having problems with one aspect, my header request is being passed to the client!
More details here: http://www.experts-exchange.com/Web/Web_Servers/Apache/Q_22074448.html I don’t want to spam your board.
Cheers,
Parto
Tiffany:
I have managed to produce an feed using your methods described here, thanks for that.
Now that I have my info in feed format, I want to tell the Feed Readers how to display it, any advice on how to make the Readers display my info in HTML within the readers (I want to put it in tables.)? My existing attempt works only in one Reader and the rest display the information but in a one line format.
Your input will be appreciated,
Jay
@Jay: you can use CSS to give it some styling, and you can also escape your HTML (for tables) by enclosing it in a CDATA section.
But why do you want it to appear in tables (unless it’s tabular data)?
what if some items in the database contain special characters that xml doesn’t like? IS there a script to replace them with xml friendly characters.
It depends on what those characters are. You can use PHP’s htmlentities() function, or enclose them in a CDATA section.
Tiffany:
You must be some kinda’ Angel or something, thanks for your help with the Feeds.
Jay
I looked all over for this. Thanks!
Wow. Thank you so much for this. I’ve been looking everywhere for a place to get started with RSS/XML, but this is the only place I’ve found that puts it nice a simple.
Hello,
Fantastic code.
I’ve done this myself, but wasn’t too sure in what format to store the “pubDate” variable in my table of the database.
Should I have one field that stores the time and date of the published feed?
Obviously this is the format : Tue, 10 Jun 2003 09:41:01 GMT, but is that the way it should be written into the db ?
Thanks tiffany, you’re a star!
@Michael: store it in the DB as a MySQL date or time stamp, then use MySQL’s UNIX_TIMESTAMP function to convert it to a Unix time stamp. Then you can manipulate it using the PHP strftime() function.
You can also store a Unix timestamp as a number, and skip the UNIX_TIMESTAMP step.
Yay I found a place that talks about what the proper header() format is for an XML document, now I can create custom, dynamic RSS feeds for my subscribers.
Thanks!
this cant work for me. When i use .htaccess the script shows white page, and other .xml in my site does not work.
help?
Thank you so much, this was so easy to implement!
Cheers!
Thanks for the tutorial. This was exactly what I wanted to do. I was considering using PHP to write my RSS feed to a separate XML file. Using AddType did not occur to me! This was faster and simpler.
Remember, depending upon your host’s server setup, certain .htaccess options may not be allowed, or .htaccess can be disabled entirely. Some problems can be explained by that. In such cases, doing as “bard” mentioned in comment #3292 may help.
Putting both the .htaccess with the AddType directive and your RSS feed in a subdirectory are good ideas anyway–parsing all XML files as PHP can interfere with declarations. That is, if you are using other XML files, or plan to in the future.
Thanks a lot!!!!
This worked out for me. Using AddType really helped a lot.
Tiffany:
Great tutorial, simple and smart. This was so easy to implement. Though I have a problem that was mentioned here before: The data in my database has special characters (i.e.: é) that cause the XML to be not valid. I tried everything but nothing seem to work. Also I have PHP 4.1 so I couldn’t try some of the newer functions, like html_entity_code.
What should I do? I’m so close!! Do you have an alternative workaround to this problem? thanks a lot from Buenos Aires, ARG.
Nicolas,
Have you tried html_entities()? That works with PHP 4.x.
Another possible solution would be to use str_replace(). With that function, you can use an array for both the find and replace parameters.
Create an array of characters that would need to be replaced and another array with their corresponding entities.
I’m new to this whole RSS thing and am having problems. Perhaps someone can help… I have done all the steps above and it appears to be ignoring everything between the <code></code> and the <code></code> tags.
It actually worked once and then when I refreshed the page, all the entries disappeared and I haven’t been able to view them again. Any ideas? I know I must be doing something wrong, but I can’t figure out
what.
Here is the RSS page… http://www.aear.org/feed/rss.php
This page shows the entires that are in the database… http://www.aear.org/stories.php
What I meant to say above is… it appears to be ignoring everything between the “End Copyright” and the “End Channel” tags.
hi. I’m on a windows server, im .htaccess wont work… please can you suggest an alternative?
thanks
tom
TOM: Rent a cheap Unix server and set it up.
@randall: no smart-assery allowed :-).
@tom: if you can’t use .htaccess, you can just keep a .php extension on the file so your server parses it correctly.
Steph, I can’t tell what the problem is by looking at the file. You may have a mis-matched variable name or some other error. Check your server error logs and see if something is wonky.
[...] Help: Creating dynamic RSS feeds Dynamic RSS feeds using PHP, MySQL and Apache :: tiffany b. brown // v 4.1 Paid Script - Ends Design :: PHP Website Development __________________ My ideas for making [...]
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.