NOTE: Wherever you see [url] please insert your websites url in place of [url].
Everywhere I look I can find tutorials but rarely are they complete. The tutorial I am about to write is meant to be a complete tutorial. Sure you can add more options to the RSS file itself but what I mean by complete is that it will work for anyone if the principals that are outlined here are followed. Without further delay lets jump right into build a dynamic RSS feed using PHP and MySQL
To begin our PHP based RSS feed we need to do one little thing first. Take a look at your “.htaccess” file which is generally located in the root directory of web server. It is the file that is used for doing URL rewrites, 301 redirects etc. Every Apache server should have it so look for it. If you are using dreamweaver an easy to get the file is to create it in the site manager, i.e. right-click and create a new file. Rename the file “.htaccess” and then right click on your newly created file and click “Get”. Again this is only for users of Dreamweaver. I am making this more complicated than it should be. Look for.htaccess in your web server root. Enough said
Once we have our.htaccess file found we need to make a change. Since our RSS file will be of the PHP extension i.e. rss.php and not of the xml type. We need.htaccess to know that files with the type.xml shoule be interpreted as.php files. To do this we enter the following in our.htaccess file:
AddType application/x-httpd-php.xml
With our.htaccess file ready to go we now need to begin writing the PHP for our RSS feed. Our file will be broken into four sections. The first is the header which tells the browser that the file is of type XML. The second section is the head of our RSS file. Its everything that you could make static about the channel, i.e. the basic info related to the RSS feed. Our third sections is where it gets nifty. We create a database connection and use it to create our RSS feed. We then need to loop through each of the items we want in our database and output them as xml. Finally our fourth part is all the output that is required to close the channel. There is little to it aside from a few echo commands but it is a separate section in its own minor way.
First we need to create a new php file, I chose to name mine rss.php, you can name yours whatever you like. Once we have our file open we begin with our first section:
//Set XML header for browser
header(‘Content-type: text/xml’);
?>
Before we do anything we want to send a message to the browser that informs it that it is dealing with an XML file. If we did not pass this information to the browser our dynamic RSS feed would not work. As simple as it sounds that is it for our first part of our RSS file.
In our next section we need to begin creating the structure of our XML file. There are few ways to do this. One is with the echo command. The other is through the use of variables. I have used both and personally prefer the variable method since it seems simpler in my mind. Therefore that is the method I will demonstrate. Here is our code (it includes the code from our first section):
//Set XML header for browser
header(‘Content-type: text/xml’);
//Create the heading/channel info for RSS Feed
$output = ”;
$output.= ”;
$output.= ”;
$output.= ‘Your RSS Feed Description’;
$output.= ‘ [url]’;
?>
Here is what we did with the second section. First we created a variable $output. We set it equal to. However once we have the value set we do not overwrite it. We instead use the operator “.=” which means simply add to the current value. So for example if we said that $some-variable = “a string”. We then used our “.=” operator to add to the value like so $some-var.= ” like some var”. Next if we were to echo our variable $some-variable it would read “a string like some var”. We are taking our variable and adding all of the xml tags to it to create one contiguous xml document stored in one variable, $output. One other thing to note is that for the title, description and link tags you should add in your sites information. While I don’t think anyone would intentionally use the info I had between the tags it is easy to forget little things like that, which is why I mention it.
For our third section we get down to the meat and bones of our php generated RSS page. What we are going to do is connect to a MySQL database and grab all the pertinent information we need. Then we are going to create individual xml items for each of the new entry or article that we have. This is all done when the user accesses the RSS page. Not before. The page is dynamic not static. For intents and purposes we don’t have an RSS page until someone accesses it. Now lets get to the code:
//Set XML header for browser
header(‘Content-type: text/xml’);
//Create the heading/channel info for RSS Feed
$output = ”;
$output.= ”;
$output.= ”;
$output.= ‘Your RSS Feed Description’;
$output.= ‘ [url]’;
//Individual Items of our RSS Feed
//Connect to a database and and display each new item in our feed
//Connect to DB
$host = “localhost”; //Name of host
$user = “cmsuser”; //User name for Database
$pass = “mypass”; //Password for Database
$db = “my_database”; //Name of Database
mysql_connect($host,$user,$pass);
mysql_select_db($db);
//Create SQL Query for our RSS feed
$sql = “SELECT `title`, `link`, `description`, `date` FROM `articles`ORDER BY `date` DESC LIMIT 0, 15”;
$result = mysql_query($sql) or die (“Query couldn’t be executed”);
//Create Loop for the individual elements in the RSS item section
while ($row = mysql_fetch_array($result))
{
$output.= ”;
$output.= ”;
$output.= ‘ ‘.$row[‘link’].”;
$output.= ”.$row[‘description’].”;
$output.= ‘
‘.$row[‘date’].”;
$output.= ”;
}
?>
Now a great deal has occurred in this section so let me try and explain everything clearly. First lets start with the comment “Connect to DB”. Here we need to connect to a database. Normally I write a function earlier on and simply call it when I want to connect to a database and run a query. However I can not assume that you have already written one so we will go through writing one together. First we define variables that will house the necessary information for the “mysql_connect” and “mysql_select_db” functions. The information we need to store is our hostname, generally its “localhost”, our user name for the database, our password, and the name of the database. Once we have that saved we use it in the function “mysql_connect” which is used to make a connection a mysql database, once we establish the connection we then need to select a database with the “mysql_select_db” statement. Now that we have connected to our database lets examine how we go about getting the information we need.
Now that we are connected we must run a query to get the information we need. For the example I have made a few assumptions the first being that the name of the database is articles and that it contains the four columns: `title`, `link`, `description`, `date` and that they are named as such. I also limited the result to 16, by using the statement “…LIMIT 0,15” which means only show rows 0 to 15. You can set it to whatever you would like or you can remove it completely and have no limit on the number of entries in your RSS feed. Okay for small sites, awful for large ones. Use your discretion here. Now that we have the query constructed I want to point out one thing. Normally you see people using the “SELECT *…” statement when they run queries. Not only do I think its bad practice but why get more information that you need, it takes longer and makes your site run just ever so slightly slower. Therefore I recommend that when you form your sql queries you implicitly state which fields you want rather than using a “SELECT *…” statement. Now that we have our query we need run it by using the command “mysql_query” and pass the results into a variable, cleverly known as $result. If you notice that after our “mysql_query($sql)” statement I have “or die(…)”. What that statement does is if there is an error it kills the query and terminates the function then echoes whatever error message you place in the brackets. Handy for figuring out where things may go wrong.
So far we have connected to a database and run a query outputting the results into a variable, $result. Now we need to put this all into a neat little RSS item. To do this we need to create a loop, What our loop will do is go through our query row by row and pull the information from each row and do whatever we want with it. In this case we want to store it. To do so we create a while loop which basically reads while there are rows still left in our result variable. We need to do whatever code is between the brackets {… }. There are other ways to have formed this loop but for now the most direct method is the one I have listed. Now that we have our rows in a variable $row we need to add them to our xml file. To do that we use our good friend “.=”and basically add in the information for each item we wish to create. There are many more tags you can use with your RSS feed. I have only chosen to use the “title”, “link”, “description”, “pubDate” since that was all I felt like i needed and this is not an article on the structure of RSS but how to generate them dynamically.
We have completed three of our four steps. Since we have all of our items created, we did it previously with the while loop that will cycle through each result in the database and add it to our variable with the appropriate tags, we need to finish off our file and display it to the user. To do this we use the following code:
//Set XML header for browser
header(‘Content-type: text/xml’);
//Create the heading/channel info for RSS Feed
$output = ”;
$output.= ”;
$output.= ”;
$output.= ‘Your RSS Feed Description’;
$output.= ‘ [url]’;
//Individual Items of our RSS Feed
//Connect to a database and and display each new item in our feed
//Connect to DB
$host = “localhost”; //Name of host
$user = “cmsuser”; //User name for Database
$pass = “mypass”; //Password for Database
$db = “my_database”; //Name of Database
mysql_connect($host,$user,$pass);
mysql_select_db($db);
//Create SQL Query for our RSS feed
$sql = “SELECT `title`, `link`, `description`, `date` FROM `articles`ORDER BY `date` DESC LIMIT 0, 15”;
$result = mysql_query($sql) or die (“Query couldn’t be executed”);
//Create Loop for the individual elements in the RSS item section
while ($row = mysql_fetch_array($result))
{
$output.= ”;
$output.= ”;
$output.= ‘ ‘.$row[‘link’].”;
$output.= ”.$row[‘description’].”;
$output.= ‘
‘.$row[‘date’].”;
$output.= ”;
}
//Close RSS channel
$output.= ”;
$output.= ”;
//Display output in browser
echo $output;
?>
Here is our completed code in all its glory, all we have added is two more statements that append our variable with the following tags “” and “” which closes our channel and RSS tags respectively, that we opened earlier. After that we need to display the information so that the web browser can see it. To do this we simply use the echo command and echo our variable $output that we used to store all the information previously. You should now be able to see what I meant earlier in the article when I suggested that one could use echo instead of adding the information to a variable, but that’s besides the point. What matters now is that you got a fully working RSS feed that you never have to toy with again unless its to add more information. Hope you enjoy it and can put it to good use!