• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1165
  • Last Modified:

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
0
freshwaterwest
Asked:
freshwaterwest
1 Solution
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
freshwaterwestAuthor Commented:
thanks ushastry - the .$row_newsfeed->rfcpubdate. was where I was going wrong
0
 
UmeshMySQL Principle Technical Support EngineerCommented:
Anytime ..

Thanks,
Umesh
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now