PHP MYSQL into RSS date format

I'm stuck with the format for the date:
(the database column date has the format 2008 12 26)

I've tried it with both the mysql SELECT and in the php, but can't get either to correctly validate (W3C feed).

SELECT `ref`, DATE_FORMAT(date,'%a, %d %b %Y %T') as dated
or
$updated = $dated("D, n M Y h:i:s");

print "<pubDate>".$updated."</pubDate>\n";

thanks
freshwaterwestAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GawaiCommented:
it should be  $date()
$updated = $date("D, n M Y h:i:s");


or try diff format
$updated = date("D, d M Y H:i:s T");
0
UmeshMySQL Principle Technical Support EngineerCommented:
Given a datetime column called pubdate, this select statement (which can be combined with other selections) will yield a column of dates named rfcpubdate formated in RFC-822 format:

SELECT DATE_FORMAT(pubdate,'%a, %d %b %Y %T') AS rfcpubdate FROM tablename WHERE 1

The full RFC-822 date includes a timezone value which is not included in the MySQL output. If using PHP, this value can be appended to the output (assuming a variable $rfcpubdate exists containing a date string from the above select statment) using:

echo " $rfcpubdate .date(T').;

This will yield output that looks like:

Mon, 19 Jun 2006 07:41:18 PDT
0
Beverley PortlockCommented:
If the database the date in the format shown, then just read the data into a PHP variable and use str_replace like so

$newDate = str_replace(" ", "-", $variableWithDateIn );

that will put the date in ISO format and allow the other date routines to work with it correctly. As far as I am aware, XML dates can be in ISO format (YYYY-MM-DD)
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

freshwaterwestAuthor Commented:
hi ushastry, I'm trying to use your suggestion, but don't know how to correctly format the echo as I have used print"" lines:

print "<pubDate>".$rfcpubdate."</pubDate>\n";

so not sure how to add the .date('T') in this context.

thanks
0
UmeshMySQL Principle Technical Support EngineerCommented:
Pls try this..
print "<pubDate>".$rfcpubdate.date('T')."</pubDate>\n";

Open in new window

0
freshwaterwestAuthor Commented:
thanks - I'm getting the following when I validate:

This feed does not validate.
line 14, column 12: pubDate must be an RFC-822 date-time: GMT (18 occurrences) [help]
<pubDate>GMT</pubDate>

when I view the feed in a browser it shows dates as current date and time rather than date from db

using:
$query_newsfeed = "SELECT DATE_FORMAT(date,'%a, %d %b %Y %T') AS rfcpubdate,

and

print "<pubDate>".$rfcpubdate.date('T')."</pubDate>\n";

0
UmeshMySQL Principle Technical Support EngineerCommented:
This is because you are using date instead of column name.. is date is column name??
0
freshwaterwestAuthor Commented:
the column name in db is date - is that what's causing the problem?
Should I change the db column name?

thanks
0
UmeshMySQL Principle Technical Support EngineerCommented:
NO need to change.. date is allowed to use... BTW can you post the table description and data type used for date column?
0
freshwaterwestAuthor Commented:
ushastry - here is the news sql - thanks

--
-- Table structure for table `news`
--

CREATE TABLE IF NOT EXISTS `news` (
  `ref` int(11) NOT NULL auto_increment,
  `date` date NOT NULL default '0000-00-00',
  `title` varchar(255) NOT NULL default '',
  `summary` longtext NOT NULL,
  `story` longtext NOT NULL,
  `attachment1` varchar(255) default '',
  `attachment1_text` varchar(255) default '',
  `attachment2` varchar(255) default '',
  `attachment2_text` varchar(255) default '',
  `image1` varchar(255) default '',
  `image1_alt` varchar(255) default NULL,
  `image2` varchar(255) default '',
  `image2_alt` varchar(255) default NULL,
  `live` int(1) NOT NULL default '0',
  `archived` int(1) NOT NULL default '0',
  `latest` int(1) NOT NULL default '0',
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`ref`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=47 ;

0
UmeshMySQL Principle Technical Support EngineerCommented:
I guess the problem is with space between $rfcpubdate & date('T')..

bcoz it expect a date time of below format

<pubDate>Wed, 02 Oct 2002 08:00:00 EST</pubDate>

But we are sending it of below format which is wrong

<pubDate>Wed, 02 Oct 2002 08:00:00EST</pubDate>


Pls try this..
print "<pubDate>".$rfcpubdate." ".date('T')."</pubDate>\n";

Open in new window

0
freshwaterwestAuthor Commented:
still getting an error in validation, also it still reads as the current date/time and not the db date:

This feed does not validate.
line 14, column 13: pubDate must be an RFC-822 date-time: GMT (18 occurrences) [help]
<pubDate> GMT</pubDate>

I've attached the whole code again just in case I've messed up elsewhere..

thanks
<?php
mysql_select_db($database_websitedb, $websitedb);
$query_newsfeed = "SELECT DATE_FORMAT(date,'%a, %d %b %Y %T') AS rfcpubdate, `ref`, title, summary, live, latest FROM news WHERE live = 1 AND archived = 0 ORDER BY `date` DESC";
$newsfeed = mysql_query($query_newsfeed, $websitedb) or die(mysql_error());
$row_newsfeed = mysql_fetch_assoc($newsfeed);
$totalRows_newsfeed = mysql_num_rows($newsfeed);
 
print "<?xml version=\"1.0\" encoding=\"ISO-8859-1\" ?>
<rss version=\"2.0\" xml:lang=\"en\" xmlns:atom=\"http://www.w3.org/2005/Atom\">
 
<channel>
  <title>The Retreat York News</title>
  <link>http://www.websitename.org.uk/news.php</link>
  <description>Feed Description</description>
  <language>en-gb</language>
	  <image><url>http://www.websitename.org.uk/images/logo.gif</url><title>The Website News</title><link>http://www.websitename.org.uk/news.php</link></image>\n";
while( $row_newsfeed = mysql_fetch_object( $newsfeed ) )
{
 
$summary = str_replace( 
                        array("&nbsp;", "&ndash;"),
                        array(" ", "-"),
                        $row_newsfeed->summary
                      );
$summary = strip_tags( $summary );
 
print "<item>\n";
print "<title>".$row_newsfeed->title."</title>\n";
print "<link>http://www.websitename.org.uk/newsitem.php?ref=".$row_newsfeed->ref."</link>\n";
print "<description>$summary</description>\n";
print "<pubDate>".$rfcpubdate." ".date('T')."</pubDate>\n";
print "<guid isPermaLink=\"true\">http://www.websitename.org.uk/newsitem.php?ref=".$row_newsfeed->ref."</guid>\n";
print "</item>\n";
}
print "<atom:link href=\"http://www.websitename.org.uk/news/rss.xml\" rel=\"self\" type=\"application/rss+xml\" />
</channel>
</rss>\n";
?>
<?php
mysql_free_result($newsfeed);
?>

Open in new window

0
UmeshMySQL Principle Technical Support EngineerCommented:



Pls try this...

I assume that below values are set

$database_websitedb
$websitedb);

<?php
mysql_select_db($database_websitedb, $websitedb);
$query_newsfeed = "SELECT DATE_FORMAT(date,'%a, %d %b %Y %T') AS rfcpubdate, `ref`, title, summary, live, latest FROM news WHERE live = 1 AND archived = 0 ORDER BY `date` DESC";
$newsfeed = mysql_query($query_newsfeed, $websitedb) or die(mysql_error());
 
###### $row_newsfeed = mysql_fetch_assoc($newsfeed);
 
$totalRows_newsfeed = mysql_num_rows($newsfeed);
 
print "<?xml version=\"1.0\" encoding=\"ISO-8859-1\" ?>
<rss version=\"2.0\" xml:lang=\"en\" xmlns:atom=\"http://www.w3.org/2005/Atom\">
 
<channel>
  <title>The Retreat York News</title>
  <link>http://www.websitename.org.uk/news.php</link>
  <description>Feed Description</description>
  <language>en-gb</language>
          <image><url>http://www.websitename.org.uk/images/logo.gif</url><title>The Website News</title><link>http://www.websitename.org.uk/news.php</link></image>\n";
while( $row_newsfeed = mysql_fetch_object( $newsfeed ) )
{
 
$summary = str_replace( 
                        array(" ", "&ndash;"),
                        array(" ", "-"),
                        $row_newsfeed->summary
                      );
$summary = strip_tags( $summary );
 
print "<item>\n";
print "<title>".$row_newsfeed->title."</title>\n";
print "<link>http://www.websitename.org.uk/newsitem.php?ref=".$row_newsfeed->ref."</link>\n";
print "<description>$summary</description>\n";
print "<pubDate>".$row_newsfeed->rfcpubdate." ".date('T')."</pubDate>\n";
print "<guid isPermaLink=\"true\">http://www.websitename.org.uk/newsitem.php?ref=".$row_newsfeed->ref."</guid>\n";
print "</item>\n";
}
print "<atom:link href=\"http://www.websitename.org.uk/news/rss.xml\" rel=\"self\" type=\"application/rss+xml\" />
</channel>
</rss>\n";
?>
<?php
mysql_free_result($newsfeed);
?>

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
freshwaterwestAuthor Commented:
thanks ushastry - the .$row_newsfeed->rfcpubdate. was where I was going wrong
0
UmeshMySQL Principle Technical Support EngineerCommented:
Anytime ..

Thanks,
Umesh
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Languages and Standards

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.