display xml pubDate from mysql database with php

[kaiman]

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

[spfoonnewb]

What column type are you storing the date in?

[kaiman]

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',

 

 

[kaiman]

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

[JAY6390]

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

[spfoonnewb]

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.

[JAY6390]

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

[spfoonnewb]

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.

[kaiman]

@ 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]

Using UNIX_TIMESTAMP doesn't alter the column or the data, just the result set. Your database will remain in tact

[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

[JAY6390]

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

[kaiman]

Okay, I will give it a whirl and let you know whether it works.

 

Thanks again,

 

kaiman

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

[JAY6390]

You have an error in your query. Change this line

'date'

to

`date`

Notice the backticks instead of the quotes

[kaiman]

No go. There must be some other error that this statement is causing:

 

I tried it as:

 

date, 'date', and `date`

 

Thanks again,

 

kaiman