Here we are going to create an RSS Feed
We will be using php and use a Mysql db to populate the feeds dynamically.
Rss has the following layout/format.
<RSS>//declare it as an rss
<CHANNEL>//open the rss channel
<ITEM>//start the Item of your RSS
<TITLE>The title of your feed item element</TITLE>
<DESCRIPTION>The description attached to the title of your feed item element</DESCRIPTION>
<URL>http://the_URL_OF A LINK_IN THE _ITEM</URL>
<guid>a unique identifier of the feed item element</guid>
<pubDate>he published date of the feed element</pubDate>
</ITEM>
</CHANNEL>
</RSS>
We start by setting up the database connection
<?php $db = new mysqli(“LOCALHOST”, “MYUSERNAME”, “MYPASS”, “DB”);?>
Then we add the declaration of the character encoding and rss version.
Below the declared encoding is windows-1252 as this is server specific to my own server, however other encoding declarations are UTF-8 and ISO-8859-1 which ever you choose is how the server encodes it, mine uses ASCII.
As we are using RSS 2.0 the content type should be application/xml
<?php header(‘Content-type: application/xml; charset=windows-1252′); ?>
<?php echo “<?”;?>xml version=”1.0″ encoding=”windows-1252″<?php echo “?>”;?>
Declare it as an RSS – version and type with link to translator – this is for Atom feed using RSS2.0.
<rss version=”2.0″ xmlns:atom=”http://www.w3.org/2005/Atom“>
Now we open the channel for the rss and add our personal data.
<channel>
<title>Your Feed Title.</title>
<description>Description of the Feed</description>
<link>http://www.YOUR WEBSITE</link>
<copyright>YOUR_NAME (C) 2009 All Rights Reserved</copyright>
<atom:link href=”http://YOURWEBSITE/feed/” rel=”self” type=”application/rss+xml” />// points to where you have uploaded the rss index.php file.
Then we Query the database. In this instance the url, title, description and date are all VARCHAR. The date has to be converted from string to a date and then into a date format accepted by RSS as pubDate such as Sat, 07 Sep 2002 00:00:01 GMT
<?php
$query = “SELECT url, title, description, pub_date, STR_TO_DATE(pub_date, ‘%d-%b-%Y’) AS sortdate, DATE_FORMAT(STR_TO_DATE(pub_date, ‘%d-%b-%Y’), ‘%d.%b.%Y’) FROM urls ORDER BY `sortdate` DESC”;
$results = $db->query($query);
$number = $results->num_rows;
for ($i = 1; $i <= $number; $i++) {
$row = $results->fetch_assoc();
$title = $row['title'];
$description = $row['description'];
$url = $row['url'];
$date = $row['sortdate'];
?>
Now we lay out the rss feed formats. You will notice that url is used for both url and guid. This is because the guid is a unique field and cannot be repeated, therefore if you have two urls the same the rss will not validate. So the guid can be anything that is unique to the feed item.
<item>
//As we are using windows encoding not UTF-8 due to server these sections need to be enlosed in [CD Data]
If not enclosed and declared as CD Data then special characters such as & ” ‘ @ etc will prevent the feed validating as it will be seen as poorly formed. so we use the ‘<![CDDATA['.$String.']]>’
<title><?php echo ‘<![CDATA['.$title.']]>’;?></title>
<description><?php echo ‘<![CDATA['.$description.']]>’; ?></description>
<link><?php echo “http://”;?><?php echo ‘<![CDATA['.$url.']]>’; ?></link>
<guid><?php echo “http://”;?><?php echo ‘<![CDATA['.$url.']]>’; ?></guid>
<pubDate><?php echo date(“D, d M Y H:i:s O”, strtotime($date)); ?></pubDate>
</item>
<?php
}
?>
Close the channel and the rss and the database connection.
</channel>
</rss>
<?php
$db->close();
?>
I have uploaded to the server and called the file index.php and placed it in a file called feed. This tells the validator that it is looking for a feed.
You can then go to W3C Atom + RSS Validator and point to the url you have uploaded the index.php rss file to and validate it as a valid RSS feed.
THE FULL CODE:
<?php $db = new mysqli(“localhost“, “username“, “password“, “DB“);?>
<?php header(‘Content-type: application/xml; charset=windows-1252′); ?>//change to UTF-8 or iso-8859-1 if required
<?php echo “<?”;?>xml version=”1.0″ encoding=”windows-1252“<?php echo “?>”;?>
<rss version=”2.0″ xmlns:atom=”http://www.w3.org/2005/Atom“>
<channel>
<title>Your Title</title>
<description>Your Description</description>
<link>http://www.YOUR_WEB</link>
<copyright>YOUR_NAME (c)2009 All Rights Reserved</copyright>
<atom:link href=”http://YOUR_LINK TO/feed/” rel=”self” type=”application/rss+xml” />
<?php // Retrieve and display the available categories.
$query = ‘SELECT * FROM url_categories WHERE url_category_id= $type’;
$results = $db->query($query);
// Retrieve the URLs for a particular category, if selected.
// Make sure the type is an integer.
if (isset($_GET['type'])) {
$type = (int) $_GET['type'];
} else {
$type = 4;
}
if ($type > 0) {
// Get the current type name.
$query = “SELECT category FROM url_categories WHERE url_category_id=$type”;
$results = $db->query($query);
$first = TRUE; // Initialize the variable.
�
// Query the database.
$query = “SELECT u.url_id, url, title, description, pub_date, STR_TO_DATE(pub_date, ‘%d-%b-%Y’) AS sortdate, DATE_FORMAT(STR_TO_DATE(pub_date, ‘%d-%b-%Y’), ‘%d.%b.%Y’) FROM urls AS u, url_associations AS ua WHERE u.url_id = ua.url_id AND ua.url_category_id=$type AND ua.approved = ‘Y’ORDER BY sortdate DESC”;
$results = $db->query($query);
$number = $results->num_rows;
for ($i = 1; $i <= $number; $i++) {
$row = $results->fetch_assoc();
$title = $row['title'];
$description = $row['description'];
$url = $row['url'];
$date = $row['sortdate'];
?>
<item>
<title><?php echo $title; ?></title>
<description><?php echo $description; ?></description>
<link><?php echo “http://”;?><?php echo $url; ?></link>
<guid><?php echo “http://”;?><?php echo $url; ?></guid>
<pubDate><?php echo date(“D, d M Y H:i:s O”, strtotime($date)); ?></pubDate>
</item>
<?php }
} ?>
</channel>
</rss>
<?php
$db->close();
?>
