RSS Feed from a MySQL Database
This tutorial will teach you how to create a simple RSS 2.0 compliant feed from a MySQL (or others with a few small changes) database.
The first thing you must do in a RSS document is to define that it is indeed an RSS feed. We do this like so…
echo '<?xml version="1.0" ?>'; echo '<rss version="2.0">';
We then must open the ‘channel’ tag, which is just our feeds main content and then give our feed some information of what it is and where it is from.
echo '<channel>'; // Opens our main content echo '<title>Test Website RSS Feed</title>'; // Defines the title of the RSS feed echo '<link>http://www.testsite.com</link>'; // Where the RSS feed is from echo '<description>This RSS feed is all about the pie.</description>'; // What its about
We then open a MySQL connection and query the server for our information, I have used a database called “test” and a table named “news”. As you can guess, this RSS feed is going to show the latest news from our test website.
$c = mysqli_connect('localhost', 'user', 'pass', 'test') or die(mysqli_error($c)); // Connect with our information $q = mysqli_query($c, 'SELECT * FROM news ORDER BY id DESC LIMIT 5') or die(mysqli_error($c)); // Query the table for 5 news articles sorting by 'id' while($r = mysqli_fetch_assoc($q)) // While there is more rows to get (max of 5) we get an associative array {
Now we want to print out each news article in a RSS compliant manner. All <item> elements (each news article) must have a title, link and description to be RSS compliant. I have added <author> and <pubDate> (published date) also. Note: <pubDate> must be in the RFC 822 standard.
echo '<item>'; // Begin a news article echo '<title>'.$r['title'].'</title>'; // Give it a title echo '<link>'.$r['permalink'].'</link>'; // The link to the article echo '<description>'.$r['content'].'</description>'; // The description or content of the article echo '<author>'.$r['author'].'</author>'; // The author echo '<pubDate>'.$r['date'].'</pubDate>'; // The date is was published echo '</item>'; // End news article } // End while statement
We then finish the document by closing our <channel> and <rss> tags.
echo '</channel>'; echo '</rss>';
I hope you enjoyed my first tutorial, please comment if you have any questions.
If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.
Comments
I remember when I first started out creating a “dynamic” feed for my website, many years ago. ‘Cause I thought the files had to end in “XML” I actually wrote to a file using my PHP script. Bad, bad!
Just making the jump from ASP to PHP and wanted to rewrite my old ASP/Access generated RSS feed, and this is a great helping hand.
@Adam
Writing to a file isn’t necessarily a bad thing. If you create a new feed file only when your content changes then you’ll be saving yourself a lot of unnecessary database calls, which is generally a good thing, particularly if you have a large number of feed subscribers.
I was hoping you would make a tutorial for atom feeds. I’m not sure the big difference between atom and RSS.
Oh my god. I surfed all over the internet after such an easy understanding guide to rss.
Really nice dude, keep up the good work.
Greetings from Danmark.
Good tutorial!
My only question is how would you save this file? It would definitely be .php but how would the file be used as an actual RSS feed? I am trying to create a quick RSS feed for my news feature on my new site, and it requires the use of MySQL databases, so w00t. If I could get that one question answered, what to save the file as and how to implement it, then I will use your code! Thanks.
And to Bjoern, if you just straight up copied/pasted this guy’s code for your use, then I think you should go read a tutorial or two on a beginner’s guide to php, mysql, xml, and a few other codes that are implemented in this script. That’s all I will say.
=Alexander=
Great tutorial Mike, thanks for your contribution