Build a Dynamic RSS Feed Using PHP/MySQL

Before we begin building our feed we need to make a few things clear:

• .htaccess is not required to make this script work.
• Any RSS feed does not have to end in .xml or .rss to work properly, it, in fact, can end in .php. (For example.)
• We want to conform to the RSS specifications to make sure our feeds work wherever they are asked to. You can find a general idea of a basic structure at W3C’s RSS 2.0 Specification page.

<rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom">
<channel>
<title>Your Website Title</title>
<description>A brief description about your website.</description>
<link>http://yourdomain.com/</link>
<copyright>Your Copyright Information</copyright>
<atom:link href="http://yourdomain.com/feed/" rel="self" type="application/rss+xml" />

There are a few pieces in here that may not be covered in the specification page that I linked you to earlier. I can’t exactly tell you why, but I can say that without your header having at least what’s provided above, you will either fail or get warnings when you validate.

Note: in our atom:link we are linking to a /feed/ subfolder. This is because the feed should be saved as index.php and placed within there. It will make things look a bit more organized on your end. The code above is the heading for the RSS. We still need to create a heading for our PHP (found below), which will be placed above the code that you already have.

<?php
  $db = new mysqli("localhost", "username", "password", "database"); // connecting database
  header('Content-type: text/xml');  // file mime type
  echo '<?xml version="1.0" encoding="iso-8859-1"?>';
  ?>

This code does a few things: it initiates a connection with the database and tells PHP to expect text/xml. Then, on the third line, we are setting the xml version and encoding. Now, it is a bit confusing because we are using PHP to echo the <? ?>, but if we don’t the script will think that we are trying to execute more PHP rather than set XML information. Hence why its best to use the traditional <?php. Now that we have all of our header information in place, let’s pull the content from the database and create a loop. Understand that my code will differ from yours, and I use the * to make it less confusing and easier to copy and paste for my readers. Be sure to change the SQL Query to match your needs and pull your content.

<?php
$query = "SELECT * FROM `table` ORDER BY `delimiter` DESC LIMIT 0,15"; // returning recent 15 results
$results = $db->query($query);
$number = $results->num_rows;
for ($i = 1; $i <= $number; $i++) {
$row = $results->fetch_assoc();
$title = $row['title'];
$description = $row['subtitle'];
$link = $row['link'];
$date = date("r", $row['timestamp']);
?>
<item>
<title><?php echo $title; ?></title>

<description><?php echo $description; ?></description>

<link><?php echo $link; ?></link>
<pubDate><?php echo $date; ?></pubDate>

<guid><?php echo $link; ?></guid>
</item>
<?php
} // end for loop
?>

This may seem like a lot but after breaking it down it really isn’t. We start by setting a query, running it, and counting how many rows of it we have. Then we set a for loop that runs depending on how many rows were returned initially, and for each instance pulling an entry and setting a new <item>. You will need to change the query to match your table, and then generate the $title, $description, $link, and $date using your own columns or format. Two things that you must understand with each item in an RSS feed: 1.) You will always want to make sure that your <pubDate> is in RFC 2822 format. This can be done using “r” in your date() function. (Php.net: date.) If your <pubDate> is not in this format, it will not be valid. 2.) Your <guid> is the entire unique URL for your entry. This is where friendly-URL’s play a big part. Lastly, we need to create a footer for our file to close the tags for the RSS and close the connection to the database:

</channel>
</rss>
<?php
$db->close(); // close db connection for better performance
?>

Now that we have all the code we need explained and filled out, this is what your entire file should look like:

<?php $db = new mysqli("localhost", "username", "password", "database"); ?>
<?php header('Content-type: text/xml'); ?>
<?php echo '<?xml version="1.0" encoding="iso-8859-1"?>';
<rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom">
<channel>
<title>Your Website Title</title>
<description>A brief description about your website.</description>
<link>http://yourdomain.com/</link>  <copyright>Your Copyright Information</copyright>
<atom:link href="http://yourdomain.com/feed/" rel="self" type="application/rss+xml" />
<?php    $query = "SELECT * FROM `table` ORDER BY `delimiter` DESC LIMIT 0,15";
$results = $db->query($query);
$number = $results->num_rows;
for ($i = 1; $i <= $number; $i++) {
$row = $results->fetch_assoc();
$title = $row['title'];
$description = $row['subtitle'];
$link = $row['link'];
$date = date("r", $row['timestamp']);
?>
<item>
<title><?php echo $title; ?></title>
<description><?php echo $description; ?></description>
<link><?php echo $link; ?></link>
<pubDate><?php echo $date; ?></pubDate>
<guid><?php echo $link; ?></guid>
</item>
<?php    }    ?>
</channel>
</rss><?php    $db->close();    ?>

It is recommended that you create a subfolder, ideally /feed/, and save this file as index.php within the folder. After saving and checking to be sure that your feed is being populated, use W3C’s Feed Validation Service to make sure you’ve done everything properly.
( Ali Han, URI:alihan.com.tr/blog )