Brainsofsteel header

Creating a RSS Feed for your Blog or Website in PHP and MySQL

Subject: Programming

Description: Creating a RSS Feed with PHP and MySQL for a Blog or Website

Posted by David Caldwell on 07/05/13 at 13:17

Now I’ve created a website for my blog using PHP and a MySQL database I will need to create a RSS feed that will be updated whenever I create a new post. 

I trawled through the internet trying to find readymade code to achieve this but was disappointed to find only paid for solutions or code so complex it made my head hurt just trying to read it.

Often when I’m stuck on a problem I’ll take a break and do something mundane instead, returning to the problem I hammered out some code and with a few tweaks it worked.

With a few more it passed the excellent WC3 Feed Validation Service which I’m pleased to say is less picky than the html mark-up validation service.

I’ll leave my code here, it might help someone.  I’m sure there’s a more elegant solution but here’s my quick and dirty solution.

I have a simple MySql table for my blog posts it's structure can be seen below.

 The php code is below

DEFINE('DATABASE_USER', 'Your User Name'); //connect to database stuff
DEFINE('DATABASE_HOST', 'localhost');
DEFINE('DATABASE_NAME', 'Your Database Name');

if (!$dbc)
trigger_error('Could not connect to MySQL: ' . mysqli_connect_error());
$query = mysqli_query($dbc, "SELECT * FROM `posts` ORDER BY DATE desc LIMIT 0,1") or die("Error: ".mysqli_error($dbc));
$cur= $query;
$lastpost = date(DATE_RSS, strtotime($row['DATE'])); //defines the last time the blog was updated
echo "<?xml version="1.0" encoding="UTF-8" ?><rss version="2.0">";
echo "<channel>";
echo "<title>Brains of Steel</title>";
echo "<description>This the RSS feed for </description>";
echo "<link></link>";
echo "<lastBuildDate>".$lastpost."</lastBuildDate>";
echo "<pubDate>".$lastpost."</pubDate>";

$query = mysqli_query($dbc, "SELECT * FROM `posts` ORDER BY DATE desc") or die("Error: ".mysqli_error($dbc));
$cur= $query;
{ //start of loop
echo "<item>";
echo "<title>".$row['TITLE']."</title>";
echo "<description>".$row['DESCRIPTION']."</description>";
echo "<guid isPermaLink="true">".rawurlencode($row['TITLE'])."</guid>"; // adds 20% to spaces in url
$rfcdate = date(DATE_RSS, strtotime($row['DATE'])); //converts datetimestamp to valid rss date format
echo "<pubDate>".$rfcdate."</pubDate>";
echo "</item>";
} //end of loop
echo "</channel>";
echo "</rss>";

This code will populate a php page with the following RSS

<?xml version="1.0" encoding="UTF-8" ?>
<rss version="2.0">
<title>Brains of Steel</title>
<description>This the RSS feed for </description>
<lastBuildDate>Tue, 07 May 2013 13:17:01 +0000</lastBuildDate>
<pubDate>Tue, 07 May 2013 13:17:01 +0000</pubDate>
<title>Creating a RSS Feed for your Blog or Website in PHP and MySQL</title>
<description>Creating a RSS Feed with PHP and MySQL for a Blog or Website</description>
<guid isPermaLink="true"></guid>
<pubDate>Tue, 07 May 2013 13:17:01 +0000</pubDate>
<title>May Day Bank Holiday</title>
<description>How Mayday is Celebrated in the UK</description>
<guid isPermaLink="true"></guid>
<pubDate>Mon, 06 May 2013 16:37:34 +0000</pubDate>

Toodlepips! Smile


Keywords: RSS, PHP, MySQL, FEED, Blog, Website