Convert date to display in RSS readers

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
ourmaninparis
Forum Newbie
Posts: 21
Joined: Wed Apr 18, 2012 8:09 am

Convert date to display in RSS readers

Post by ourmaninparis »

Hello,

I've created an RSS feed using PHP. All works correctly except that I cannot get the date to display. In the MySQL database the date is in format :

YYYY-MM-DD HH:ii:SS

The field is format DATETIME.

Here is a sample of my file:

Code: Select all

for($i=0;$i<20; $i++) {

$subject = mysql_result($result,$i,'t1.Notice_Title');
$description = mysql_result($result,$i,'t1.Notice_Text');
// Clean the description
$description = str_replace ("&","",htmlspecialchars(strip_tags($description)));
$link = mysql_result($result,$i,'t1.Notice_Link');
//to record when the feed was published
$pubdate = mysql_result($result,$i,'t2.publish_up');



echo '
<item>
<title>'.$subject.'</title>
<link>'.$link.'</link>
<description>'.$description.'</description>
<pubDate>'.$pubdate.'</pubDate>
</item>
';

Any suggestions for getting this to display?
ourmaninparis
Forum Newbie
Posts: 21
Joined: Wed Apr 18, 2012 8:09 am

Re: Convert date to display in RSS readers

Post by ourmaninparis »

Just tried this but it isn't working:

Code: Select all

for($i=0;$i<20; $i++) {

$subject = mysql_result($result,$i,'t1.Notice_Title');
$description = mysql_result($result,$i,'t1.Notice_Text');
// Clean the description
$description = str_replace ("&","",htmlspecialchars(strip_tags($description)));
$link = mysql_result($result,$i,'t1.Notice_Link');
//to record when the feed was published
$pubdate = mysql_result($result,$i,'t2.publish_up');

echo '
<item>
<title>'.$subject.'</title>
<link>'.$link.'</link>
<description>'.$description.'</description>
<pubDate>date("r", strtotime($DATA[.$pubdate.]))</pubDate>
</item>
';

} //end of the for-loop

User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Convert date to display in RSS readers

Post by Christopher »

You did not show your SQL. I'd recommend using DATE_FORMAT() in your query and letting MySQL format the date.
(#10850)
x_mutatis_mutandis_x
Forum Contributor
Posts: 160
Joined: Tue Apr 17, 2012 12:57 pm

Re: Convert date to display in RSS readers

Post by x_mutatis_mutandis_x »

Code: Select all

for($i=0;$i<20; $i++) {
 
$subject = mysql_result($result,$i,'t1.Notice_Title');
 $description = mysql_result($result,$i,'t1.Notice_Text');
 // Clean the description
 $description = str_replace ("&","",htmlspecialchars(strip_tags($description)));
 $link = mysql_result($result,$i,'t1.Notice_Link');
 //to record when the feed was published
 $pubdate = mysql_result($result,$i,'t2.publish_up');

 //format the date
 $format = 'm/d/Y'; //or what ever format you choose (see the reference below)
 $pubdate = date_format(date_create($pubdate), $format);
 
echo '
 <item>
 <title>'.$subject.'</title>
 <link>'.$link.'</link>
 <description>'.$description.'</description>
 <pubDate>'.$pubdate.'</pubDate>
 </item>
 ';
 
} //end of the for-loop
For reference: http://www.php.net/manual/en/function.date.php
ourmaninparis
Forum Newbie
Posts: 21
Joined: Wed Apr 18, 2012 8:09 am

Re: Convert date to display in RSS readers

Post by ourmaninparis »

Works a treat! Thanks very much :)
User avatar
tr0gd0rr
Forum Contributor
Posts: 305
Joined: Thu May 11, 2006 8:58 pm
Location: Utah, USA

Re: Convert date to display in RSS readers

Post by tr0gd0rr »

Oh gosh don't format dates in MySQL!
Four reasons off the top of my head:
  1. If you ever need to change to SQL Server or Oracle or whatever you have to rewrite queries.
  2. The designer or html writer should have control over date format. The choice of date format is largely an aesthetic one.
  3. If you want to internationalize your app, you may want different date formats for different locales.
  4. You may want to let users choose the date format.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Convert date to display in RSS readers

Post by pickle »

2), 3) ,4) are invalid in this instance because this is for an RSS reader, so the date has to be in the RSS accepted standard format. 1) is a valid concern
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Convert date to display in RSS readers

Post by Christopher »

I don't think 1) is much of a reason either. People rarely, if ever, convert from one database to another. And when you do you have to review all the SQL anyway and are sure to catch any functions used. I agree that 2), 3) ,4), which are all about pushing the date format into the template are very valid reasons if there is a need for creativity in date formatting. Usually app centralize this kind of formatting so it can be changed site-wide with a setting and not have to dig through templates, especially for 3).
(#10850)
x_mutatis_mutandis_x
Forum Contributor
Posts: 160
Joined: Tue Apr 17, 2012 12:57 pm

Re: Convert date to display in RSS readers

Post by x_mutatis_mutandis_x »

Christopher wrote:I don't think 1) is much of a reason either. People rarely, if ever, convert from one database to another. And when you do you have to review all the SQL anyway and are sure to catch any functions used. I agree that 2), 3) ,4), which are all about pushing the date format into the template are very valid reasons if there is a need for creativity in date formatting. Usually app centralize this kind of formatting so it can be changed site-wide with a setting and not have to dig through templates, especially for 3).
And especially if you use an ORM, then they have abstracted SQL (Hibernate HQL, Doctrine DQL etc) accross platforms as well in which case 1) is not a reason at all
2), 3), 4) could be a reason if your building a UI app but I aggree with pickle, when it's a standard-acceptable format.
User avatar
tr0gd0rr
Forum Contributor
Posts: 305
Joined: Thu May 11, 2006 8:58 pm
Location: Utah, USA

Re: Convert date to display in RSS readers

Post by tr0gd0rr »

Yes, definitely 2,3,4 are irrelevant in this instance. As far as 1, after I spent several painful months to convert a whole app to work with both MySQL and SQL Server, I made it a rule to always use standard SQL that works across platforms. And yes, when you use an ORM, that becomes a lot easier.

I would also add a #5: separation of concerns. The code that interacts with the database should know nothing about how the data is presented. You may have many display adapters for the same exact data (e.g. mobile html, json api, xml api, xls, etc.) Now of course you will probably use strtotime on the data sometime after you get it from the database (so it doesn't hurt anything that the database is formatting the date) but the concept of separating concerns is important.

I just wanted to make a point to any noobs coming across Christopher's comment that you shouldn't interpret his suggestion as a general best practice to format dates at the database level.
Post Reply