MySQL Driven RSS Feed

After your website begins to create a mountain of dynamic content, it might be time for you to consider creating an RSS feed. RSS feeds allow you to reach a broad range of people through relevant broadcasting channels. Backlinks are also possible with RSS Feeds because your feed will contain links back to the original content. You might even be able to integrate your RSS feed for people who use mobile devices that like your content. All those people need to do is to check your RSS feed to see if you have provided new content.

While RSS feeds deliver a great deal of benefits, they are also very simple to create. XML is the language used to display your RSS feed in a standard format for all devices and browsers to understand. There is also a naming standard for the elements and structure of your XML. Like I said, creating an RSS feed is very easy, so you will be able to create one while reading this tutorial.

RSS feeds are not limited to dynamic websites, but you need to find a way to dynamically add elements to the RSS feed. We will use PHP to create output your XML. Let's create our first RSS feed!

<?php

header("Content-Type: application/xml; charset=utf-8"); 

function linkElement($title, $description, $articleUrl, $dateCreated) {

	echo 	'<item>'. PHP_EOL;

	echo 		'<title>'.$title.'</title>'. PHP_EOL;

	echo 		'<description>'.$description.'</description>'. PHP_EOL;

	echo 		'<link>'.$articleUrl.'/</link>'. PHP_EOL;

	echo 		'<pubDate>'.gmdate(DATE_RSS, strtotime($dateCreated)).'</pubDate>'. PHP_EOL;

	echo 		'<guid isPermaLink="true">'.$articleUrl.'</guid>'. PHP_EOL;

	echo 	'</item>'. PHP_EOL;	

}

//start rss output

echo '<?xml version="1.0" encoding="utf-8"?>'. PHP_EOL;

echo '<?xml-stylesheet type="text/css" href="http://www.afterhoursprogramming.com/styles/rss.css"?>'. PHP_EOL;

echo '<rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom">'. PHP_EOL;

echo 	'<channel>'. PHP_EOL;

echo	"<title>Web Design and Development Tutorials</title>". PHP_EOL;

echo 	'<link>http://www.afterhoursprogramming.com</link>'. PHP_EOL;

echo 	'<description>Tutorials and References for HTML, CSS, JavaScript, PHP, ColdFusion, Python, SQL, SEO, Graphic Design, and Information Architecture.</description>'. PHP_EOL;

echo 	'<language>en-us</language>'. PHP_EOL;

echo 	'<copyright>Copyright (C) 2013 AfterHoursProgramming.com</copyright>'. PHP_EOL;

echo 	'<atom:link href="http://www.afterhoursprogramming.com/services/rss/" rel="self" type="application/rss+xml" />'. PHP_EOL;



//add elements here

linkElement("After Hours Programming", "An Overview to PHP","http://www.afterhoursprogramming.com/tutorial/PHP/Overview/",'2012-08-15');

linkElement("After Hours Programming", "An Overview to JavaScript","http://www.afterhoursprogramming.com/tutorial/JavaScript/Overview/",'2012-07-05');

//end of adding elements



echo 	'</channel>'. PHP_EOL;

echo '</rss>'. PHP_EOL;

?>

The example will output a perfect RSS feed XML document. We construct the first few elements using our basic website information. All you need to do is replace the After Hours Programming content with yours. Then, we move onto creating the items of the RSS feed, which are each for a unique web page. However, the example above is not ideal because you certainly do not want to add a statement inside that file every time you create some new content. The best way to create a dynamic RSS feed is to store pages inside a database table.

MySQL is one of the best database languages for a low budget because it's free and easy to work with. When I say create a table for your pages or content, I do not just mean to store a link in the table. You could certainly create a table of links, but why not just store your content or pages in the table as well? Since we are making an RSS feed, we want our newest content first. Our content table will have a creation date and a modified date that will help us organize our RSS Feed. So, now let's build a MySQL table...

Field: content_id, title, description, content, url, creation_date, update_date
Type: int, varchar(200), varchar(500), text, varchar(500), datetime, datetime
Extra: auto_increment

You can create tables with whatever database software is provider to you by your host. The MySQL software I am using is Starfield, which is nothing special. You only need to fill in the field, type, with your content_id having the exception of setting the extra field to "auto_increment". After you have created your table, go ahead and add some records to the "content" table. Now that we have our table with a few records, we can final create our RSS Feed based on our MySQL table.

//add elements here

$hostName = 'your-host-name'; // Modify this value

$dbName = 'your-database-name'; // Modify this value

$username = 'your-username'; // Modify this value

$password = 'your-password'; // Modify this value

$dbConnection = new PDO("mysql:host=".$hostName.";dbname=".$dbName, $username, $password);

$sql = 'SELECT title, description, url, creation_date 

		FROM content

		ORDER by creation_date DESC

		LIMIT 25';

$query = $dbConnection->prepare($sql);

$query->execute();

$queryResults = $query->fetchAll();

foreach ($queryResults as $result) {

   linkElement($result['title'], $result['description'], $result['url'], $result['creation_date']);

}

//end of adding elements

If you looked at the first example. we are completely replacing the old static calls to our "linkElement" function with some dynamic code. We use PHP's PDO class to connect to our MySQL database. If you haven't ever used the PDO class you should really look into it. The $sql variable holds our query that allows us to get the 25 most recent records in our "content" table. After that, we prepare the SQL and execute it. Finally, we arrive to the dynamic part of the RSS feed. As we run through all of our records in the foreach loop, we use the "linkElement" function that we created before to output RSS elements for each "content" record. Of course, you can modify these examples to create an even better RSS Feed, but this is a simple example of a MySQL driven RSS feed XML document.

RSS feeds provide a great way to communicate with your users even when they are not on your website. Also, you can submit your RSS feed to various websites that showcase your RSS feed so you can attract new users.