RSS has become the standard technology for syndicating information to large audiences. Many people have something to say, but its finding the right audience for your voice that matters. A great place to start is by creating your own RSS feed and adding to it as often as you can.
In this article you'll learn how to syndicate your own custom RSS feeds using PHP and MySQL. We'll first learn how to create two database tables and then how to retrieve data from them which will be formatted into an RSS feed. Here's an example of the completed RSS feed and a download link to the code. Let's start by taking a look at how to create the MySQL database tables.
Creating the MySQL RSS Tables
An RSS feed consists of the main details for the feed, such as the title, description, url, image and so on. Next are the items, probably the most important parts of the feed. i.e., different stories from a newspaper, posts from a blog and so on. Therefore, we will create two database tables, the first is called webref_rss_details, which contains the details for the feed and the second is called webref_rss_items, which contains all of the items. If you would like to get a better idea of the RSS structure you can take a look at The Anatomy of an RSS Feed, which is a previous article I wrote on the basics of the RSS structure.
In order to get started with the database tables, use the following code, which can be found in the webref_rss_details.sql file, to create the webref_rss_details table
In order to get started with the database tables, use the following code, which can be found in the webref_rss_details.sql file, to create the webref_rss_details table:
1 CREATE TABLE 'webref_rss_details' (
2 'id' int(11) NOT NULL auto_increment,
3 'title' text NOT NULL,
4 'description' mediumtext NOT NULL,
5 'link' text,
6 'language' text,
7 'image_title' text,
8 'image_url' text,
9 'image_link' text,
10 'image_width' text,
11 'image_height' text,
12 PRIMARY KEY ('id')
13 ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
view plain | print | ?
CREATE TABLE 'webref_rss_details' ( 'id' int(11) NOT NULL auto_increment, 'title' text NOT NULL, 'description' mediumtext NOT NULL, 'link' text, 'language' text, 'image_title' text, 'image_url' text, 'image_link' text, 'image_width' text, 'image_height' text, PRIMARY KEY ('id') ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
The following code, which can be found in the webref_rss_items.sql file, will create the webref_rss_items table:
1 CREATE TABLE 'webref_rss_items' (
2 'id' int(11) NOT NULL auto_increment,
3 'title' text NOT NULL,
4 'description' mediumtext NOT NULL,
5 'link' text,
6 PRIMARY KEY ('id')
7 ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
view plain | print | ?
CREATE TABLE 'webref_rss_items' ( 'id' int(11) NOT NULL auto_increment, 'title' text NOT NULL, 'description' mediumtext NOT NULL, 'link' text, PRIMARY KEY ('id') ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Our first table, webref_rss_details, contains 10 columns, which are the id, title, description, link, language, image_title, image_url, image_link, image_width and image_height. Each of these columns are self-explainatory once you are familiar with the structure of an RSS feed, the non-RSS related column is the id, which is used to represent the id for the row of data in the database. The second table, webref_rss_items, contains 4 columns, which include the id, title, description and link. In the example I'm linking to the home page of the current Web site, but this URL would typically link to the location of the original text, such as a specific blog page where an entry has been posted. Now that we officially have the MySQL tables created, add the data that you want to syndicate, then proceed to the next section and take a look at how to construct a valid RSS 2.0 feed with PHP.
Creating a valid RSS 2.0 feed with PHP
Once the database tables are in place we'll create three PHP files. The first is called index.php, which belongs in the root of our site directory, the second RSS.class.php, which belongs in a directory called classes and the third is mysql_connet.php, which should be placed in a directory that is inaccessible to the outside world. For the purposes of this article I placed the sample file in the same directory as the RSS class. In the index.php file, start by adding a header that will configure the content type of the document as valid XML and choose the charset that you need. Next, include the RSS class, instantiate the object and trigger a method called GetFeed. The GetFeed method will return the actual RSS feed once it has been constructed in the class, so perform an echo on the return value to write the data to the index file.
1 <?
2 header("Content-Type: application/xml; charset=ISO-8859-1");
3 include("classes/RSS.class.php");
4 $rss = new RSS();
5 echo $rss->GetFeed();
6 ?>
7
view plain | print | ?
<? header("Content-Type: application/xml; charset=ISO-8859-1"); include("classes/RSS.class.php"); $rss = new RSS(); echo $rss->GetFeed(); ?>
The mysql_connect.php file defines our database connection information, makes the initial connection and selects the defined database.
1 <?
2 DEFINE ('DB_USER', 'your_username');
3 DEFINE ('DB_PASSWORD', 'your_password');
4 DEFINE ('DB_HOST', 'localhost');
5 DEFINE ('DB_NAME', 'your_databasename');
6
7 // Make the connnection and then select the database.
8 $dbc = @mysql_connect (DB_HOST, DB_USER, DB_PASSWORD) OR die ('Could not connect to MySQL: ' . mysql_error() );
9 mysql_select_db (DB_NAME) OR die ('Could not select the database: ' . mysql_error() );
10 ?>
view plain | print | ?
<? DEFINE ('DB_USER', 'your_username'); DEFINE ('DB_PASSWORD', 'your_password'); DEFINE ('DB_HOST', 'localhost'); DEFINE ('DB_NAME', 'your_databasename'); // Make the connnection and then select the database. $dbc = @mysql_connect (DB_HOST, DB_USER, DB_PASSWORD) OR die ('Could not connect to MySQL: ' . mysql_error() ); mysql_select_db (DB_NAME) OR die ('Could not select the database: ' . mysql_error() ); ?>
The RSS class constructor is a public method that requires the mysql_connect.php file. Remember to put this file in a safe location on your server so it's not exposed to hackers. Once the file's in place change the path in the RSS constructor. The next method in the RSS class is GetFeed, which is a public method that we're calling from the index. This method will concatenate the details and items and return a valid RSS 2.0 structure. The first method it calls is a private method called getDetails. This method makes a connection to our webref_rss_details table and selects all of the values. Once the results are retreived the RSS file is constructed from the database data in a while loop that iterates the rows in the database. Once complete the getDetails method returns the details for the RSS structure.
The second method that GetFeed calls is a private method called getItems. This method selects all of the values in the webref_rss_items table and iterates them. While iterating the values a string of XML is constructed that represents the item structure for the RSS file. Once complete, the getItems method returns the item structure to the GetFeed method, which in turn returns the entire RSS structure to the index to be written to the page.
1 <?
2
3 class RSS
4 {
5 public function RSS()
6 {
7 require_once ('pathto.../mysql_connect.php');
8 }
9
10 public function GetFeed()
11 {
12 return $this->getDetails() . $this->getItems();
13 }
14
15 private function dbConnect()
16 {
17 DEFINE ('LINK', mysql_connect (DB_HOST, DB_USER, DB_PASSWORD));
18 }
19
20 private function getDetails()
21 {
22 $detailsTable = "webref_rss_details";
23 $this->dbConnect($detailsTable);
24 $query = "SELECT * FROM ". $detailsTable;
25 $result = mysql_db_query (DB_NAME, $query, LINK);
26
27 while($row = mysql_fetch_array($result))
28 {
29 $details = '<?xml version="1.0" encoding="ISO-8859-1" ?>
30 <rss version="2.0">
31 <channel>
32 <title>'. $row['title'] .'</title>
33 <link>'. $row['link'] .'</link>
34 <description>'. $row['description'] .'</description>
35 <language>'. $row['language'] .'</language>
36 <image>
37 <title>'. $row['image_title'] .'</title>
38 <url>'. $row['image_url'] .'</url>
39 <link>'. $row['image_link'] .'</link>
40 <width>'. $row['image_width'] .'</width>
41 <height>'. $row['image_height'] .'</height>
42 </image>';
43 }
44 return $details;
45 }
46
47 private function getItems()
48 {
49 $itemsTable = "webref_rss_items";
50 $this->dbConnect($itemsTable);
51 $query = "SELECT * FROM ". $itemsTable;
52 $result = mysql_db_query (DB_NAME, $query, LINK);
53 $items = '';
54 while($row = mysql_fetch_array($result))
55 {
56 $items .= '<item>
57 <title>'. $row["title"] .'</title>
58 <link>'. $row["link"] .'</link>
59 <description><![CDATA['. $row["description"] .']]></description>
60 </item>';
61 }
62 $items .= '</channel>
63 </rss>';
64 return $items;
65 }
66
67 }
68
69 ?>
view plain | print | ?
Conclusion
This code sample is a good start for a complex custom RSS feed creator. We could easily create a form that adds or updates new items in our database. For now I give you the base because I'm sure you have many ideas of your own. Armed with this new RSS file you can go to a Web site such as FeedBurner to make it easier to deliver your content to the public.
Download Source Code