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 // set the file's content type and character set // retrieve database records $db = mysql_connect("YourMySQLSERVER", "YOURUSERNAME", "YOURPASSWORD"); // store items from the database in the $result1 array $query1 = "SELECT faqarticles.question, faqarticles.content, SUBSTRING(content,1,600) as mycontent, // display RSS 2.0 channel information ECHO <<
function cleanText($intext) {
return utf8_encode(
htmlspecialchars(
stripslashes($intext)));
}
// this must be called before any output
header("Content-Type: text/xml;charset=utf-8");
if (!$db)
{
error_log("Error: Could not connect to database in rss.php.");
exit;
}
mysql_select_db("knowledgebase");
faqarticles.cat, faqarticles.id, faqarticles.date FROM faqarticles WHERE
faqarticles.approved != '1'
ORDER BY faqarticles.date DESC";
$result1 = mysql_query($query1);
$phpversion = phpversion();
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 <<
END;
}
ECHO << ?>
Written by Tim on February 16th, 2006 with
9 comments.
END;
Read more articles on php.
#1. December 11th, 2007, at 8:02 AM.
Wish that code was still up.