I'm upgrading all of my feeds to RSS 2.0 so the other syndication format evangelists won't make fun of me. On sites that I publish using PHP and MySQL, I've been offering extremely basic RSS 0.92 feeds that blow up when unusual characters like "¿" are used.

Here's my new MySQL-to-RSS 2.0 PHP script and sourcecode output.

To clean up text from a MySQL database for presentation as XML, the script includes the following function:

function cleanText($intext) {
  return utf8_encode(
    htmlspecialchars(
      stripslashes($intext)));
}

Working my way out of that return statement, the stripslashes method removes the backslash in front of single quote marks ('), double quote marks (") and backslashes (). This replaces escaped character codes, which are often used when storing text in a MySQL database, with the characters they represent.

The htmlspecialchars method turns some characters into HTML entity codes: "<" becomes <, ">" becomes >, and "&" becomes &. These characters have special meaning in XML data, so one way to represent them correctly is to replace them with entities.

The utf8_encode method converts text to UTF-8 format, the default character set for XML, which I decided to use in my RSS feed. I think this cures my problem with unusual characters.

I use another function, date, to turn a MySQL date into a RSS 2.0 timestamp:

@$row = mysql_fetch_array($result1);
$pubDate = date("r", strtotime($row["dateadded"]));

The first argument to the function, "r", establishes the format of the date, RFC 2822, which is compatible with RSS 2.0.

The last upgrade to my script is to stop using URLs as guid values, adopting the Tag URI instead:

$guid = "tag:cadenhead.org,2004-05-20:java21days.".   $row["dex"];

I've always been uncertain about the best way to use guid. Normally, I make it an item's permanent link, adopting the same value as the link element.

The purpose of guid, which provides a unique identifier for each feed item, is enhanced when the value never changes. Using a URL for this value negates that benefit when Web resources change domain names and URLs.

Using the Tag URI has an additional advantage: Looking at the feed, the purposes of link and guid are more clear.

I placed rss.php into the public domain through Creative Commons, so the code can be used in any manner. If you run into any problems on your own MySQL data, let me know so I can improve it.

Comments

Hi Rogers,

I've been trying to use your mysql to rss code to extract news items from mysql database with no success. Here is the output (error) I'm getting:


-
-
Psychic News
www.voicesfrombeyond.com
Psychic News
en-us
www.voicesfrombeyond.com
PHP/4.4.1

Warning
: mysql_num_rows(): supplied argument is not a valid MySQL result resource in
/home/voicesfr/public_html/
news/psychics8.php

on line
66



Line 66 begins with:

for ($i = 0; $i < mysql_num_rows($result1); $i++) { @$row = mysql_fetch_array($result1); $title = "Day ".cleanText($row["Chapter"])." Update"; $link = "www.voicesfrombeyond.com".

If you can help me get this working I will appreciate it. What does chapter represent in you code?

:
:
:

Popular Pages on This Site