Programming a MySQL Fed RSS Feed in PHP: Part 2

Posted on Fri, 04 May 2007
 at 17:18:47

     Yesterday I began by showing you how to create the RSS file itself. Today we will go through the code behind this file. I have a suspicion that this will be a long post, so let's get started.

     OK, all of the code on this page goes into the file rss.class.php. The clue is in the filename - it contains a class called RSS, so we begin by declaring this class:

class RSS {

     Within this class we have all the functions the rss.php file used to get the feed to display. The first function called by rss.php is getLastBuildDate(), so I will go through this function first.

     getLastBuildDate() returns the date of the last entry in the table. This will tell any aggregators if the feed has been updated since they last downloaded it. The function looks like this:

function getLastBuildDate() {
            // Get the date of the last entry
            $sql = "SELECT * FROM table ORDER BY `ID` DESC"; // Build the query
            // Connect to the database
            /*
             * INSERT YOUR CONNECTION CODE HERE!
            */
            // Run the query
            $result = mysql_query($sql);
            // Check for error
            if(!$result) {
                mysql_close();
                return 0;
            }
            // Put all the stuff into a variable and return it
            $loop = 1;
            while(($values = mysql_fetch_object( $result ))
                  && ($loop == 1)) {
                $lastbuilddate = $values->Date;
                $loop = 0;
            }
            mysql_close();
            return $lastbuilddate;
        }

     The function is pretty simple. All it does is run a query and return the date of the first entry.

     Next rss.php calls the generatefeed() function. As you can probably guess from the name, this function will return the feed contents.

function generatefeed() {
            $sql = "SELECT * FROM table ORDER BY `ID` DESC"; // Build the query
            // Connect to the database
            /*
             * INSERT YOUR CONNECTION CODE HERE!
            */
            // Run the query
            $result = mysql_query($sql);
            // Check for error
            if(!$result) {
                mysql_close();
                return 0;
            }
            // Put all the stuff into a variable and return it
            while($values = mysql_fetch_object( $result )) {
                $rssfeed .= "<item><title>".strip_tags($values->Title)."</title> <link>".$values->URL."</link> <description>".strip_tags($values->Description)."</description> <pubDate>".$values->Date."</pubDate></item>";
                $number++;
            }
            mysql_close();
            return $rssfeed;
}

     This function is not too dissimilar to getLastBuildDate(), except that it returns a variable with much more content.

     It's worth noting the ORDER BY statements. I have done it by ID because where I use this code the one with the highest ID is always the most recent, however this could easily be modified to suit your needs, for instance, to order by the Date replace ORDER BY `ID` with ORDER BY `Date`.

     Make sure also that when you use the above functions you put in the correct table name. At the moment the table name is "table" in the SQL statements.

     To finish everything up close the class and save. To make your life easier here is the complete page in it's entirety:

<?php
    class RSS {
        // Generate the site's RSS feed
        function getLastBuildDate() {
            // Get the date of the last entry
            $sql = "SELECT * FROM table ORDER BY `ID` DESC"; // Build the query
            // Connect to the database
            /*
             * INSERT YOUR CONNECTION CODE HERE!
            */
            // Run the query
            $result = mysql_query($sql);
            // Checfk for error
            if(!$result) {
                mysql_close();
                return 0;
            }
            // Put all the stuff into an array and return it
            $loop = 1;
            while(($values = mysql_fetch_object( $result ))
                  && ($loop == 1)) {
                $lastbuilddate = $values->Date;
                $loop = 0;
            }
            mysql_close();
            return $lastbuilddate;
        }
       
        function generatefeed() {
            $sql = "SELECT * FROM table ORDER BY `ID` DESC"; // Build the query
            // Connect to the database
            /*
             * INSERT YOUR CONNECTION CODE HERE!
            */
            // Run the query
            $result = mysql_query($sql);
            // Check for error
            if(!$result) {
                mysql_close();
                return 0;
            }
            // Put all the stuff into an array and return it
            while($values = mysql_fetch_object( $result )) {
                $rssfeed .= "<item><title>".strip_tags($values->Title)."</title> <link>".$values->URL."</link> <description>".strip_tags($values->Description)."</description> <pubDate>".$values->Date."</pubDate></item>";
                $number++;
            }
            mysql_close();
            return $rssfeed;
        }   
    }
?>

     I hope you have found this tutorial useful, and I would appreciate any comments you have and attempt to answer any questions, so either post a comment here or e-mail dan [at] dan-blog [dot] com and I will do my best to help you.

---

Dan said...
Thanks for posting that.  I'm also a college student trying to get into web-development.  I've just started messing with PHP so this is great.

...on Fri, 04 May 2007 at 23:12:34.

---

---

---