Hello... I'm new to php and XML, and am trying to create an RSS feed
using php/MySQL.  It's strange... if I add this to MyYahoo as an RSS
feed it shows up. But if I run it through a validator, it doesn't
validate (an extra line is added to the top of the file). And when I
look at it through IE, I see what looks like a valid RSS feed about
1/3 of the time, the rest it gives errors.

What I would like is someone to figure out what's wrong with the
script I've written, fix it (so that it validates), and give me a
description of what they did (so I can fix it myself the next time).

The script is based off of this one:
http://www.xmlmania.com/documents_article_34.php

The validator I'm using is:
http://feedvalidator.org/

Thanks!

Hi, 

The biggest problem with the demo script is that the dates are not
formatted correctly. I've changed that, and my validation is fine.
However, what I noticed within their ( the site you got this script
from) news feed:
http://www.xmlmania.com/xml/feeds/news.php
is  they have HTML formatting inside the DESCRIPTION tags, which will
not validate. Most of the time the browsers and RSS Readers know what
to do with HTML coding inside the DESCRIPTION tag, but the validators
do not, because it is not valid RSS.

Some other notes just to help out, is the SQL table should not have
the name of a MySQL keyword as a field name, such as TEXT or DATE. 
Something like this would be more appropriate.

create table rssnews(
id int(10) not null auto_increment primary key,
title varchar(200),
newstext text,
newsdate datetime
);

And the revised PHP code is here:

---BEGIN CODE --- 
<?php

// change the sql look up so that you can format the date correctly// 
// 



header ("Content-type: text/xml");

echo ("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n");
?>
<rdf:RDF xmlns:dc="http://purl.org/dc/elements/1.1/"
xmlns:h="http://www.w3.org/1999/xhtml"
xmlns:hr="http://www.w3.org/2000/08/w3c-synd/#"
xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#"
xmlns="http://purl.org/rss/1.0/">
    <channel rdf:about="http://www.scarn.com/rss/news.php">
        <title>News Feed</title>
        <description>News Feed Test for RSS</description>
        <link>http://www.scarn.com/rss/news.php</link>
    </channel>

<?php
mysql_connect ("localhost", "testman", "testword") or die ("Cannot
connect to database server.");
mysql_select_db ("test") or die ("Cannot connect to database.");


// change the sql look up so that you can format the date correctly// 
// $result = mysql_query ("select * from rssnews order by id desc
limit 0,10") or die (mysql_error());
$myq = 'SELECT id, title, newstext, DATE_FORMAT( newsdate,
\'%Y-%m-%d\' ) AS mydate'
        . ' FROM rssnews'
        . ' ORDER BY id DESC '
        . ' LIMIT 0 , 10';
$result = mysql_query($myq) or die (mysql_error());



// change the item to items as directed by the RSS Validator// 

while ($row = mysql_fetch_array ($result)) {
    echo ("    <item rdf:about=\"http://www.scarn.com\">
        <title>");
    echo $row['title'];
    echo ("</title>
        <description>");
    echo $row['newstext'];
    echo ("</description>
        <link>http://www.scarn.com/news_article_");
    echo $row['id'];
    echo (".php");
    echo ("</link>
        <dc:date>");
// USE NEW DATE FORMAT // 
    echo $row['mydate'];
    echo ("</dc:date>
    </item>\n\n");
}

mysql_free_result ($result);
?></rdf:RDF>

--- END CODE ---

Some Resources 

W3 Date Formats

Different standards may need different levels of granularity in the
date and time, so this profile defines six levels. Standards that
reference this profile should specify one or more of these
granularities. If a given standard allows more than one granularity,
it should specify the meaning of the dates and times with reduced
precision, for example, the result of comparing two dates with
different precisions.
http://www.w3.org/TR/NOTE-datetime

DATE_FORMAT(date,format)
    Formats the date value according to the format string. The
following specifiers may be used in the format string:
http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html

Some thoughts:

The whole article shouldn't be in a RSS Feed anyway, so not having the
HTML formatting in there shouldn't be a problem, but, if you feel it
is, and you want to be spared the task of having two separate areas
(website text data and the RSS Feed text data), you can use something
like what they are showing on this website to change the outgoing text

http://www.php.net/manual/en/function.ob-start.php

Down in the example it shows how to use the call back function to
alter text using str_replace.

Personally, I would not put the HTML in there at all, and have a
separate table for the RSS feed, so that I could have HTML formatting
on my page. It is a bit more work, but this way it will always
validate, and any RSS reader will be able to see it without problems.

If you have any other questions regarding this please use the
Clarification Request button. I will be happy to help you out with
anything else on this script that doesn't seem to be working properly.

thanks, 

webadept-ga

---

Request for Answer Clarification by muonwar-ga on 13 Jan 2005 11:42 PST

Hello... sorry for the delay in responding.  I would like a
clarification.  I modified the demo script (and should have included
it in the first post -- this was my first time w/this).  Everything
said about the first part is still true, is it still the dates here
that's causing it to not validate?  There shouldn't be any HTML in
this one, btw.

---- 
<?php
header ("Content-type: application/rdf+xml");
// Output file as XML
echo ("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n");
// The XML declaration
?>
<rdf:RDF xmlns:dc="http://purl.org/dc/elements/1.1/"
	xmlns:h="http://www.w3.org/1999/xhtml"
	xmlns:hr="http://www.w3.org/2000/08/w3c-synd/#"
	xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#"
	xmlns="http://purl.org/rss/1.0/">
<channel rdf:about="http://www.foo.com/folder/file.php">
<title>Latest Stuff</title>
<description>Browse the latest stuff.</description>
<link>http://www.foo.com</link>
</channel>
<?php
include("../admin/config.php");
$sql = "SELECT category, adtext, id FROM spot WHERE postdate >= NOW()
- INTERVAL 2 DAY AND status = 'a' ORDER BY RAND() DESC LIMIT 3";
$result = mysql_query($sql, $conn) or die (mysql_error());
while ($row = mysql_fetch_array ($result)) {
echo (" <item rdf:about=\"http://www.foo.com\">
<title>");
echo $row[category];
echo ("</title>
<description>");
echo $row[adtext];
echo ("</description>
<link>http://www.foo.com/folder/file");
echo (".php?id=");
echo $row[id];
echo ("</link>
<dc:date>");
echo $row[date];
echo ("</dc:date>
</item>\n\n");
}
mysql_free_result ($result);
?></rdf:RDF>

-----

Thanks for the help.

---

Clarification of Answer by webadept-ga on 13 Jan 2005 12:38 PST

Thanks for this clarification. I'll go over what you have posted here
and get back to you soon.

webadept-ga

---

Clarification of Answer by webadept-ga on 20 Jan 2005 19:50 PST

Hi, sorry for not getting back to you sooner, you caught me in the
middle of a move.

I would have to say yes, the date format would be incorrect in the
example you posted here, since it is not being changed and MySQL
defaults to YYYY-MM-DD format, which is not RSS compliant.

One of the things that is a bit troubling with RSS checkers is that
when a field is not formatted correctly, then all of the levels down
to that field are also reported as in error. From what I can see here,
it looks like only the date will need to be changed.

If you can zip up your code, and the data structure from MySQL you are
using(with just a few records in the data tables) and post that on the
web someplace where I can download the zip file, I would be happy to
load it up and debug it for you.

thanks, 

webadept-ga

---

Request for Answer Clarification by muonwar-ga on 26 Jan 2005 08:36 PST

Thanks, can I email it to you?  If not, let me know and I'll post it
where you can download it. Thanks!

---

Clarification of Answer by webadept-ga on 26 Jan 2005 11:47 PST

Sorry, we don't have direct email addresses. If you can post it, I can
download it and send you the edits.

thanks again, 

webadept-ga

---

Request for Answer Clarification by muonwar-ga on 02 Feb 2005 07:41 PST

Hi there... sorry for the delay.  Here's the code, could you try this?  Thanks!

--
<?php
header ("Content-type: application/rdf+xml");
// Output file as XML
echo ("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n");
// The XML declaration
?>
<rdf:RDF xmlns:dc="http://purl.org/dc/elements/1.1/"
	xmlns:h="http://www.w3.org/1999/xhtml"
	xmlns:hr="http://www.w3.org/2000/08/w3c-synd/#"
	xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#"
	xmlns="http://purl.org/rss/1.0/">
<channel rdf:about="http://www.foo.com/news/news.php">
<title>Latest Stuff</title>
<description>Browse the latest.</description>
<link>http://www.foo.com</link>
</channel>
<?php
include("../admin/config.php");
$sql = "SELECT category, adtext, id FROM classifieds WHERE postdate >=
NOW() - INTERVAL 2 DAY AND status = 'a' ORDER BY RAND() DESC LIMIT 3";
$result = mysql_query($sql, $conn) or die (mysql_error());
while ($row = mysql_fetch_array ($result)) {
echo (" <item rdf:about=\"http://www.foo.com\">
<title>");
echo $row[category];
echo ("</title>
<description>");
echo $row[adtext];
echo ("</description>
<link>http://www.foo.com/post/contact-seller");
echo (".php?id=");
echo $row[id];
echo ("</link>
<dc:date>");
echo $row[date];
echo ("</dc:date>
</item>\n\n");
}
mysql_free_result ($result);
?></rdf:RDF>

---

Clarification of Answer by webadept-ga on 02 Feb 2005 08:14 PST

Hey, 

No problem, I'll load that up and see what we have here. 

webadpet-ga

---

Clarification of Answer by webadept-ga on 02 Feb 2005 08:17 PST

Could you also post the SQL for the table(s) this is accessing, I'll
put in info for testing, but it would be better to work with the table
as you have it created.

webadept-ga

---

Request for Answer Clarification by muonwar-ga on 02 Feb 2005 11:14 PST

-- phpMyAdmin SQL Dump -- version 2.6.0-pl3 --
http://www.phpmyadmin.net -- -- Host: localhost -- Server version:
3.23.58 -- PHP Version: 4.3.10 -- -- Database: `gville` -- --
-------------------------------------------------------- -- -- Table
structure for table `classifieds` -- CREATE TABLE `classifieds` ( `id`
mediumint(6) unsigned NOT NULL auto_increment, `postdate`
timestamp(14) NOT NULL, `xp` date default NULL, `category` varchar(75)
NOT NULL default '', `email` varchar(50) NOT NULL default '', `phone`
varchar(12) NOT NULL default '', `adtext` text, `newsletter`
set('yes','no','current') default NULL, `ip` varchar(15) default NULL,
`status` set('v','a','r','p','d') default 'p', `password` varchar(16)
binary default NULL, `style` varchar(25) default NULL, `photo`
varchar(75) default NULL, `weblink` varchar(75) default NULL,
`company` varchar(50) default NULL, `cool` set('y','n') default 'n',
PRIMARY KEY (`id`), KEY `category` (`category`), KEY `xp` (`xp`) )
TYPE=MyISAM AUTO_INCREMENT=15640 ; -- -- Dumping data for table
`classifieds` -- INSERT INTO `classifieds` VALUES (15152,
'20050114081814', '2005-02-14', 'Transportation : Boats & Planes :
Over $10,000', 'seahunter186@hotmail.com', '352-307-5007', '03 seahunt
186 escape 19ft dual console 03 yamaha 115 2stroke,deep vee all
fiberglass, bimini top, vhf radio, cd stereo,fish finder, rod holders,
large livewell and fish box, in dash cooler, aluminum trailer all the
water toys included and a gps full of gulf coast fishing spots for
grouper trout and snapper $16,500', 'yes', '209.40.25.14', 'r',
0x69726d75696b71626f686a6e62773374, NULL, NULL, NULL, NULL, '');
INSERT INTO `classifieds` VALUES (15153, '20050114081814',
'2005-02-14', 'Transportation : Boats & Planes : Over $10,000',
'seahunter186@hotmail.com', '352-307-5007', '03 seahunt 186 escape
19ft dual console 03 yamaha 115 2stroke,deep vee all fiberglass,
bimini top, vhf radio, cd stereo,fish finder, rod holders, large
livewell and fish box, in dash cooler, aluminum trailer all the water
toys included and a gps full of gulf coast fishing spots for grouper
trout and snapper $16,500', 'yes', '209.40.25.14', 'a',
0x7a3737357174733464376b34386b6e71, NULL, NULL, NULL, NULL, '');
INSERT INTO `classifieds` VALUES (15154, '20050114081834',
'0000-00-00', '', '', '', '', '', '209.40.25.14', 'r',
0x646431616c7666336d693261726d6366, NULL, NULL, NULL, NULL, '');
INSERT INTO `classifieds` VALUES (15155, '20050114081834',
'0000-00-00', '', '', '', '', '', '209.40.25.14', 'r',
0x627370763934736c326c75747a716b6c, NULL, NULL, NULL, NULL, '');

---

Clarification of Answer by webadept-ga on 02 Feb 2005 14:50 PST

Thanks, 

I'll get back to you on this. 

webadept-ga

Waiting on clarification.