Building an RSS Feed From a Database

on Thu Aug 28 11:30:34 GMT 2008 in PHP and viewed 15357 times

Learn how to take information in a MySQL database table and export it to RSS using the file writing capabilities of PHP.

RSS, or Rich Site Summary is something that has boomed across the internet. The overall basics, is that RSS is a quick, easy way to access information without loading up a page and scanning for updates.

I’ve covered the basics of creating RSS in another article already, the My First RSS Feed article. But in this article, you’re going to learn how to make and update the RSS feed automatically using PHP.

If you want to see the basic end result, you can check out the mock demo I made of it where you can add your own rows and it will create an RSS file from it named rss.xml.

Mock-up demo of creating RSS feeds from the database.

The server automatically deletes all the rows and the rss feed every day at midnight GMT time.

To Start – The Database Outline

To start you need your overall view of the database you’re going to be extracting the information for the feed from. This will be a simple little news table. So connect to MySQL however you want to, and run this table creation query:


CREATE TABLE news (id int PRIMARY KEY AUTO_INCREMENT,
title varchar(255),
content text);

After this you need to fill it with some data to make sure you see the result, so…


INSERT INTO news VALUES ('', 'test', 'test content');
INSERT INTO news VALUES ('', 'test2', 'more content');

Now you have your database set up! You’re about 1/3 of the way there. Ish.

Thinking Things Through

So now you have to decide how it is you’re going to write to the file. What’s the structure of your code? Find out the things that change, and the things that don’t. So let’s start by taking a look at the RSS code:


<?xml version="1.0"?>
<rss version="2.0">
<channel>

<title>The Shadow Fox Feed</title>
<link>http://www.shadow-fox.net</link>
<description>Feed Description</description>
<language>en-us</language>

<item>
<title>Title 1</title>
<link>http://www.yoururlhere.com/1</link>
<description>Your Content</description>
</item>

<item>
<title>Title 2</title>
<link>http://www.yoururlhere.com/2</link>
<description>More of your content</description>
</item>

</channel>
</rss>

Looking at the file, we can see a couple things that don’t change: There’ll always be the rss definition at the front and the end of the channel and rss elements at the end. Also, there’ll always be the item elements at the beginning and end of the rss items. You’ll probably have a loop for those. And the title, link, and description stays the same too with the addition of a few variables. Knowing this, why don’t you start your PHP file now?

Adding the PHP

The first thing you’ll probably want to do is to fetch the rows from the database. It could help a bit. So to start, we’ll need that query.


<?php

$select = "SELECT * FROM news ORDER BY id DESC LIMIT 5";
$query = mysql_query($select) or die(mysql_error());

?>

If you didn’t read the article about basic MySQL, what this does is it will select all of the fields in each row, meaning the title, id, and the content. There’ll only be 5 entries included in the RSS feed, and it will sort by newest to oldest.

Now that’s a pretty basic query. Let’s get into the nitty gritty of writing the file.


$file = fopen("rss.xml", "w");
fwrite($file, "<?xml version="1.0"?>
<rss version="2.0">
<channel>
<title>The Shadow Fox Feed</title>
<link>http://www.shadow-fox.net</link>
<description>Feed Description</description>
<language>en-us</language>");

//The database fetching and item writing goes here

fwrite($file, "</channel></rss>");

fclose($file);

Wow, what a rush. So your file will be pretty boring at the moment, you’ll get there. At least this code deals with the three important functions in file management: fopen, fwrite, fclose.

Line 1 of the file deals with actually making the file. fopen() takes two arguments, the first being the actual file, and the second being the mode in which to open the file. You also need to assign it to a variable. And opening it in mode “w” means that it will overwrite the whole file, and if the file doesn’t exist, it will try to create it.

The second function, fwrite(), does the bulk of the work and also takes two functions. The first is the variable that had been previously assigned to the file. The second is the actual text to be written to the file.

And the last is fclose, which just takes the file to be closed. It can be left out, but it’s still great to put it in there.

Now we have your basic idea for the PHP. The only thing really left to do is to get the information from the database and write that to the file as well. So why don’t you structure out the basics of the database part.


while($array = mysql_fetch_array($query)){
extract($array);
$content = htmlentities($content);
echo $title.", ".$date.", ".$id
}

That should output every row that you have in your database from our previous SELECT query. Then it’ll show you the title, date, and the unique id of the rows. You should also escape the content, in case of something that will make the XML either not validate, or just break the whole PHP application. That’s all done by the htmlentities() function.

Now that that’s done, it’s time to actually get into the items. You need to substitute each field into the appropriate item fields. Those are usually the title, the link, and the description.


while($array = mysql_fetch_array($query)){
extract($array);
$content = htmlentities($content);
fwrite($file, "<item>
<title>$title</title>
<link>http://www.shadow-fox.net/index.php?blog=post&amp;id=$id</link>
<description>$content</description>
</item>");
}

And there’s your code! You should be familiar with all of the previous parts of the RSS format from the other article. This is all written to the files through use of the fwrite() function, again. And this will loop until each row has been written to the file. Let’s look at the whole code for the whole great file now:


<?php

$connection = mysql_connect("localhost", "username", "password");
mysql_select_db("database", $connection);

$select = "SELECT * FROM news ORDER BY id DESC LIMIT 5";
$query = mysql_query($select) or die(mysql_error());

$file = fopen("rss.xml", "w");

fwrite($file, "<?xml version="1.0"?>
<rss version="2.0">
<channel>
<title>The Shadow Fox Feed</title>
<link>http://www.shadow-fox.net</link>
<description>Feed Description</description>
<language>en-us</language>");

while($array = mysql_fetch_array($query)){
extract($array);

$content = htmlentities($content);

fwrite($file, "<item>
<title>$title</title>
<link>http://www.shadow-fox.net/index.php?blog=post&amp;id=$id</link>
<description>$content</description>
</item>");
}
//end of while loop

fwrite($file, "</channel></rss>");

fclose($file);

?>

There you go! No new code has been added, it’s a conglomeration with the previous code.

To revisit the code, let’s look at it:

First you should connect to your database. Afterwards, you query the database for the latest five news articles. Then you open or create a file to write, and start by writing the first part of the RSS feed. Then we loop through each news item and write that to the file as well. When that’s done with, the finish of the channel and of the rss is added to the file and it is closed!

Congrats, it’s a great big file-writin’ database-fetchin’ monster! Now you have your very own rss feed maker. Feel free to customize this to your whim, this is just a skin-and-bones demonstration anyways. Go out, feed, and prosper.

Be sure to check out the mockup of an interface I made. It will create rows in the database and make a file as well. You can see how the script actually does work.

Mock-up demo of creating RSS feeds from the database.

The Shadow Fox Network - Ruby on Rails, PHP, XHTML, CSS, Javascript, and XML Tutorials

Building an RSS Feed From a Database

Comments