February 16th, 2006

You are currently browsing the articles from TechToolBlog written on February 16th, 2006.

RSS Feed any MySQL Database using PHP

To some folks rss is this hot new technology that is changing our lives in technology. The truth is rss is a specific formated text file that people finally have standarized on. It’s the standarization that changes people lives, not neccessary the technology. Anyway, a while back I wanted to rss feed our knowledgebase. The knowledgebase articles are stored in a mysql database. I played around with using PEAR::PACKAGE::XML to do the job but found it to be a bit overkill. So I wrote my own xml builder - source code is below or you can download the php rss feed mysql database code. What is nice, is that you can RSS feed any database.

// prepare HTML text for use as UTF-8 character data in XML function cleanText($intext) { return utf8_encode( htmlspecialchars( stripslashes($intext))); }


// set the file's content type and character set

// this must be called before any output

header("Content-Type: text/xml;charset=utf-8");
// retrieve database records
$db = mysql_connect("YourMySQLSERVER", "YOURUSERNAME", "YOURPASSWORD");

if (!$db)

{

   error_log("Error: Could not connect to database in rss.php.");

   exit;

}
// store items from the database in the $result1 array

mysql_select_db("knowledgebase");
$query1 = "SELECT faqarticles.question, faqarticles.content, SUBSTRING(content,1,600) as mycontent,

          faqarticles.cat, faqarticles.id, faqarticles.date FROM faqarticles WHERE

          faqarticles.approved != '1'

          ORDER BY faqarticles.date DESC";

$result1 = mysql_query($query1);

$phpversion = phpversion();
// display RSS 2.0 channel information
ECHO <<




   
http://www.yourlinkgoeshere.com
      Describe your RSS Feed Heredescription>

      en-us

      http://backend.userland.com/rss

      PHP/$phpversion
    

      http://www.yoursite.com/images/YourLogo.gif
http://www.yoursite.com/index.php
      140

      60

      Describe Your Image

    

END;
// loop through the array pulling database fields for each item

for ($i = 0; $i < mysql_num_rows($result1); $i++) {

   @$row = mysql_fetch_array($result1);

   $title = cleanText($row["question"]);

   $link = "http://www.YourSite.com/kb/index.php?article=".$row["id"];

   $description =  $row["mycontent"];
   //Replace Ugly HTML that got into the Knowledgebase with nothing

   $desc_replace = array("
 
“, “ 
“, );

   $desc_replace_with  = array(”", “”, “”);

   $desc_temp = str_replace($desc_replace, $desc_replace_with, $description);
   //Now clean the HTML

   $mydescription = cleanText($desc_temp);

   $pubDate = $row[”date”];
// display an item

ECHO <<
	
$link
		$mydescription… For the entire article, please visit our site.
$pubDate
	

END;
}
ECHO <<
   



END;