Reading RSS Data from a Database (Optional) - MySQL

While it is good to have an RSS reader that can read any RSS document, it would be even better if you could store that information in a database and read it at your leisure when you are not connected to the Internet. It would also be good to be able to update your RSS file through the use of the database. It is relatively easy to achieve this, so let's create a table from which we will add our data:

CREATE TABLE `rss_tbl` (

  `feed_id` int(5) NOT NULL auto_increment,

  `title` varchar(200) NOT NULL default '',

  `link` varchar(200) NOT NULL default '',

  `description` text NOT NULL,

  `the_date` date NOT NULL default '0000-00-00',

  PRIMARY KEY  (`feed_id`)

) TYPE=MyISAM AUTO_INCREMENT=1 ;

 The table will store the individual links as they are read in by the RSS reader. Fill this table with data, using the following format:

• Title – the title of your story.
  
•  Link – The link to your story.
  
•  Description – A short description of your story.

You can then use this data to write to your RSS file :

<?

$fp=fopen(“myrssfile”, “w+”);

if (!$fp){

echo “error opening file”;

exit;

}else{

$query1="Select *,DATE_FORMAT(the_date,'%W,%d %b %Y') as thedate
FROM rss_tbl WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) ORDER BY
the_date DESC LIMIT 10 ";

$result=mysql_query($query1);

while($row=mysql_fetch_assoc($result)){

fwrite($fp,$row[‘title’]."\r\n");

fwrite($fp,$row[‘link’]."\r\n");

fwrite($fp,$row[‘description’]."\r\n");

fwrite($fp,$row[‘thedate’]."\r\n");

fwrite($fp, ” ”);

}//endwhile

fclose($fp);

}//end else

This code does two things. First, it opens (or creates) a file called "myrssfile": 

$fp=fopen(“myrssfile”, “w+”);

The "w+" instructs PHP to create the file if it does not exist and to overwrite any contents that it might have. Then it checks to see if there are any problems opening the file: 

if (!$fp){

echo “error opening file”;

exit;

If there are problems, the program displays a message and stops execution. If every thing is okay, a SQL query is run that retrieves ten articles from the database that were created in the last thirty days: 

 $query1="Select *,DATE_FORMAT(the_date,'%W,%d %b %Y') as
thedate FROM rss_tbl WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY)
ORDER BY the_date DESC LIMIT 10 ";

The DATE_FORMAT() function enables us to format the date column in what ever fashion we like. After this the code writes the database data to the file:

fwrite($fp,$row[‘title’]."\r\n");

fwrite($fp,$row[‘link’]."\r\n");

fwrite($fp,$row[‘description’]."\r\n");

fwrite($fp,$row[‘thedate’]."\r\n");

fwrite($fp, ” ”);

That’s it. A file called "myrssfile" should now be available and contain ten articles from the database. With small changes to the table you can expand the database usage and create an RSS aggregator, which is like a online "newspaper" that is entirely made up of RSS feeds from different websites.

To actually enter the data into the database, you only need to create a form that will take the necessary input values and write them to the table. In one of the articles that I wrote about RSS, I discuss how to create and populate an RSS file through a form. Although in that particular article we transfer data from a form to a file, with some small changes you can transfer the data from a form to a database.

Conclusion

To use this code make sure to include “xmlparser.php” in whatever page you are using. Then just call the “parsefile(“yourRSSfileLocation”)” function and your file data will parsed. Also, you might have noticed that in some news sites, the news headlines are scrolling from right to left on the screen. You can achieve this by using the <marquee> HTML tag; Google it to find out how to use it.

Download the xmlparser.php here. This is the same file we link to at the beginning of this article. Next, I will be discussing how to build a RSS File. Till then have fun.