Jump to content

display xml pubDate from mysql database with php


kaiman

Recommended Posts

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>';  
?>

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>';  
?>

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

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.

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

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.

@ 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

@ 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

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>[email protected]</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>';  
?>

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.