kaiman Posted January 23, 2010 Share Posted January 23, 2010 I am trying to display the publication date a news article from a database in xml via php, but it won't show up. There is no errors it just gets to the text rown and then doesn't display anything else. My question: What is the correct way to display dates from a database in xml via php? Below is my code. All help is appreciated. kaiman <?php header('content-type: text/xml'); // connects to server and selects database. include ("dbconnect.inc.php"); // table name $tbl_name="news"; // select info from database $sql="SELECT * FROM $tbl_name ORDER BY id DESC LIMIT 0, 10"; $result=mysql_query($sql); while ($row=mysql_fetch_array($result)){ $row_date = strtotime($row['date']); // display xml page echo '<?xml version="1.0" encoding="UTF-8"?> <rss version="2.0"> <channel> <title>My Site</title> <description>My Site News Feed</description> <copyright>Copyright '.date('Y').' My Site</copyright> <language>en-us</language> <link>http://www.mysite.com/</link>'; echo '<item>\n <title>'.$row['title'].'</title> <description><![CDATA[<p> '.$row['text'].' </p>]]></description> <link>http://www.mysite.com/news/</link> <pubDate>'.date('F, j, Y \a\t g:i A, T', $row_date).'</pubDate> <managingEditor>'.$row['author'].'</managingEditor> </item>'; } echo '</channel> </rss>'; ?> Quote Link to comment Share on other sites More sharing options...
spfoonnewb Posted January 23, 2010 Share Posted January 23, 2010 What column type are you storing the date in? Quote Link to comment Share on other sites More sharing options...
kaiman Posted January 23, 2010 Author Share Posted January 23, 2010 The date is stored in the db like this and the timestamp is set to NOW() from another script that adds the news via a form: `date` datetime NOT NULL default '0000-00-00 00:00:00', Quote Link to comment Share on other sites More sharing options...
kaiman Posted January 23, 2010 Author Share Posted January 23, 2010 I am still looking for help. I tried using the DATE_FORMAT function in mysql but without any success. Here is what I now have: <?php header('content-type: text/xml'); // connects to server and selects database. include ("dbconnect.inc.php"); // table name $tbl_name="news"; // select info from database $sql="SELECT *, DATE_FORMAT('date', '%W, %M %e, %Y, %k:%s %p') as date FROM $tbl_name ORDER BY date DESC LIMIT 0, 10"; $result=mysql_query($sql); while ($row=mysql_fetch_array($result)){ // display xml page echo '<?xml version="1.0" encoding="UTF-8"?> <rss version="2.0"> <channel> <title>My Site</title> <description>My Site News Feed</description> <copyright>Copyright '.date('Y').' My Site</copyright> <language>en-us</language> <link>http://www.mysite.com/</link>'; echo '<item>\n <title>'.$row['title'].'</title> <description><![CDATA[<p> '.$row['text'].' </p>]]></description> <link>http://www.mysite.com/news/</link> <pubDate>'.$row['date'].'</pubDate> <managingEditor>'.$row['author'].'</managingEditor> </item>'; } echo '</channel> </rss>'; ?> Quote Link to comment Share on other sites More sharing options...
JAY6390 Posted January 23, 2010 Share Posted January 23, 2010 Retrieve the date by using the UNIX_TIMESTAMP to convert your time into a unix timestamp with mysql, then in your php use date('r') which will format it in the RFC 2822 format. This should work fine. I am assuming you are using this for an RSS feed, not just XML in general Quote Link to comment Share on other sites More sharing options...
spfoonnewb Posted January 23, 2010 Share Posted January 23, 2010 You could use UNIX_TIMESTAMP(date) and then convert it in PHP, or change your current query to this: SELECT *, DATE_FORMAT(date, '%W, %M %e, %Y, %k:%s %p') as date FROM $tbl_name ORDER BY date DESC LIMIT 0, 10 The value returns null when you use single quotes around the column name within the function. Quote Link to comment Share on other sites More sharing options...
JAY6390 Posted January 23, 2010 Share Posted January 23, 2010 I don't think you can modify the value of date and assign it back to date again can you? Also date is a keyword so you can't just use it like that. You would need to do something like SELECT *, DATE_FORMAT(`date`, '%W, %M %e, %Y, %k:%s %p') as `pubdate` FROM $tbl_name ORDER BY date DESC LIMIT 0, 10 Quote Link to comment Share on other sites More sharing options...
spfoonnewb Posted January 23, 2010 Share Posted January 23, 2010 Yes you can, this query works fine: SELECT *, DATE_FORMAT(date, '%W, %M %e, %Y, %k:%s %p') as date FROM $tbl_name ORDER BY date DESC LIMIT 0, 10 and yes you can do this as well: SELECT *, DATE_FORMAT(`date`, '%W, %M %e, %Y, %k:%s %p') as date FROM $tbl_name ORDER BY date DESC LIMIT 0, 10 but there is no need to do so. Quote Link to comment Share on other sites More sharing options...
kaiman Posted January 23, 2010 Author Share Posted January 23, 2010 @ JAY6390 I did something similar to your recommendation and changed the value of date to something else, but that did not seem to work. @ spfoonnewb I am afraid that if I change my date database field to UNIX_TIMESTAMP versus datetime it will blow up 3 other scripts that are running off of that database table for my news and comments page. Also, another issue that is occuring is that Safari can't seem to handle this file as a feed when it is saved as a php file versus an xml file. Is there a way to save it as a xml file and get php to recognize it? i.e. feed.php > feed.php.xml? Thanks for the continued help, kaiman Quote Link to comment Share on other sites More sharing options...
JAY6390 Posted January 23, 2010 Share Posted January 23, 2010 Using UNIX_TIMESTAMP doesn't alter the column or the data, just the result set. Your database will remain in tact Quote Link to comment Share on other sites More sharing options...
kaiman Posted January 23, 2010 Author Share Posted January 23, 2010 @ JAY6390 Please excuse my ignorance, but I haven't used UNIX_TIMESTAMP in this instance before (I am not a professional developer). Could I use something like this? SELECT *, UNIX_TIMESTAMP(`date`, '%W, %M %e, %Y, %k:%s %p') as `pubdate` FROM $tbl_name ORDER BY date DESC LIMIT 0, 10 And then just return this? $row['date']; Thanks again, kaiman Quote Link to comment Share on other sites More sharing options...
JAY6390 Posted January 23, 2010 Share Posted January 23, 2010 You could use $row['pubdate'] MySQL will make the temporary field 'pubdate' with the data formatted as you want it, so you can just use that Quote Link to comment Share on other sites More sharing options...
kaiman Posted January 23, 2010 Author Share Posted January 23, 2010 Okay, I will give it a whirl and let you know whether it works. Thanks again, kaiman Quote Link to comment Share on other sites More sharing options...
kaiman Posted January 23, 2010 Author Share Posted January 23, 2010 Okay now I am getting a mysql error: <b>Warning</b>: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in <b>/home/mysite/public_html/projects/rft/rss/index.php</b> on line <b>14</b><br /> supposedly on line 14 which is this line: while ($row=mysql_fetch_array($result)){ Here is my complete code: <?php header('content-type: text/xml'); // connects to server and selects database. include ("dbconnect.inc.php"); // table name $tbl_name="news"; // select info from database $sql="SELECT *, UNIX_TIMESTAMP('date', '%W, %M %e, %Y %k:%s %p') as date_added FROM $tbl_name ORDER BY date DESC LIMIT 0, 10"; $result=mysql_query($sql); while ($row=mysql_fetch_array($result)){ // display xml page echo '<?xml version="1.0" encoding="UTF-8"?> <rss version="2.0"> <channel> <title>My Site</title> <description>My Site News Feed</description> <link>http://www.mysite.com/</link> <copyright>Copyright '.date('Y').' My Site</copyright> <language>en-us</language> <lastBuildDate>'.$row['date_added'].'</lastBuildDate> <managingEditor>'.$row['author'].'</managingEditor> <pubDate>'.$row['date_added'].'</pubDate> <webMaster>news@mysite.com</webMaster>'; echo '<item>\n <title>'.$row['title'].'</title> <description><![CDATA[<p> '.$row['text'].' </p>]]></description> <link>http://www.mysite.com/news/</link> <managingEditor>'.$row['author'].'</managingEditor> <pubDate>'.$row['date_added'].'</pubDate> </item>'; } echo '</channel> </rss>'; ?> Quote Link to comment Share on other sites More sharing options...
JAY6390 Posted January 23, 2010 Share Posted January 23, 2010 You have an error in your query. Change this line 'date' to `date` Notice the backticks instead of the quotes Quote Link to comment Share on other sites More sharing options...
kaiman Posted January 23, 2010 Author Share Posted January 23, 2010 No go. There must be some other error that this statement is causing: I tried it as: date, 'date', and `date` Thanks again, kaiman Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.