In the final article of this series we’re going to write a script in PHP that will dynamically create an RSS feed from entries in a MySQL database.  If you decide to use this script in your own custom application, you may need to change parts of the script, such as the query.  You can check out the script here.

Let’s take a look at the script to see how it works.

The first part will define some parameters to establish a database connection.  This includes information such as the host, username, password, and the database.

This next part is important.

header(‘Content-type: text/xml’);
print “<?xml version=\”1.0\”?>\n”;

This sends the appropriate header information to the client telling it that this is in fact an XML file. Without this line, your feed will show up as plain text and will be read as such.

After the header information, we begin the actual RSS feed. This should look familiar as it was covered in the previous article. Simply fill this information out as it relates to your site or have it retrieve the information from another source, such as your database.

<?php
 // Connection to db
 $mysqldb = new mysqli(HOST, USER, PASS, DB);
 // Query to select posts

 $query = “SELECT * FROM posts”;
 $result = $mysqldb->query($query);
 if($result->num_rows) {
  while($row = $result->fetch_array(MYSQLI_ASSOC)) {
?>

   <item>
    <title><?php echo $row['post_title']; ?></title>
    <link><?php echo $row['post_link']; ?></link>
    <author><?php echo $row['post_author']; ?></author>
    <description><?php echo $row['post_content']; ?></description>
   </item>
<?php 
  }
 }
?>

This is the part of the code that fetches the posts from the database and turns them into the RSS feed. Simply retrieve all of your posts from the databse and use a while loop to print them all out as items of the feed. Once the loop is done, close the channel and RSS tags and you’re done!  You now have an RSS feed that will update itself when you update your MySQL database.

In the previous article I demonstrated a practical application of RSS with Google Reader.

Now that we have a working example of RSS in action, let’s dig a bit deeper and see what’s inside an RSS feed. For this article, we’re going to use RSS 2.0 as our primary example.

An RSS feed is made up of two main parts: information about our feed and the items in our feed.

Information about our feed describe what the feed is all about.  It includes elements such as the blog title, link to our blog and description of our blog. All of this information is mandatory.

The other part (items) can be thought of as each individual blog post. Each item has elements that describe the post. These elements include the title of the post, the link to the full post, the full content or snippet of the post, the author, and date published. All of these elements except the author and publication date is mandatory.

Let’s take a look at a sample RSS file

<?xml version="1.0"?>
<rss version="2.0">
        <channel>
        <title>nickpeters.net</title>
        <link>http://www.nickpeters.net</link>
        <description>Blog of Nick Peters</description>
        <item>
                <title>Blog Post 1</title>
                <link>http://www.nickpeters.net/blog-post-1</link>
                <author>Nick Peters</author>
                <description>This is a Blog Post!</description>
		<pubDate>Mon, 26 Mar 2007 09:39:21 GMT</pubDate>

        </item>
	<item>
                <title>Blog Post 2</title>
                <link>http://www.nickpeters.net/blog-post-2</link>
                <author>Nick Peters</author>
                <description>This is a another Blog Post!</description>
		<pubDate>Tue, 27 Mar 2007 011:52:38 GMT</pubDate>

        </item>
        </channel>
</rss>

Let’s first take note that this information is very minimal, but it should be enough to get key ideas across. A full list of elements can be found here.

From looking at the information above we can see that the name our blog is “nickpeters.net,” the URL of the blog is http://www.nickpeters.net and the description is “Blog of Nick Peters.”

This blog has two posts: “Blog Post 1” and “Blog Post 2.” Each post has a link, author, description and publication date.

RSS feeds are pretty self explanatory; it’s an XML file that describes the contents of a blog for easy manipulation in clients such as RSS readers.

Next we will see how easy it is to create an RSS feed using PHP and MySQL.