ATOM FEED / PHP / SQL Help

[craigy72]

Hello,

 

I've been rying for hours to get this to work and was wondering if anyone had any solutions.

 

I'm trying to build an ATOM feed from an MS SQL DB.

 

The problem is that an ATOM feed requires RFC 3339 date format but when I output the results from my database it doesn't come out in this format. Therefore, the feed becomes invalid.

 

I've tried a function that I stole off of another website but that doesn't work either, e.g. output for date = 1209641520 (not RFC 3339 Format - 2007-01-01T00:00:02Z)

 

Here is my code:

 

<?php

header ("Content-type: application/atom+xml");

echo ("<?xml version=\"1.0\" encoding=\"utf-8\"?>");

?>

 

<feed xmlns="http://www.w3.org/2005/Atom">

<title> xxxxx </title>

<link rel = "self" href ="http://xxxxxx.com/phptest/feedbuildatom3.php"/>

<id>http://www.xxxxx.net/</id>

<updated>2007-01-01T00:00:02Z</updated>

 

<author>

<name>Craig xxxx</name>

<email>[email protected]</email>

 

</author>

 

 

 

<?php

$servername = 'localhost';

$username = 'cfitches';

$password = 'welcome';

$msconnect = mssql_connect($servername, $username, $password);

$msdb = mssql_select_db ("dashtest", $msconnect);

$msquery = "SELECT CAST(url AS TEXT) AS url, title, CAST(summary AS TEXT) AS summary, COALESCE (publish_date, create_date) AS publish_date, dbo.rep_subject_theme.theme_name, mime_type, exposure_impact FROM dbo.rep_subject_theme JOIN dbo.tmp_article ON dbo.rep_subject_theme.subject_theme_id = dbo.tmp_article.subject_theme_id WHERE search_event_id = '230' AND profile_id = '32' ORDER BY publish_date DESC;";

$doGet=mssql_query($msquery);

 

?>

 

<?php function parse_date($date)

{   

if (preg_match('/([0-9]{2,4})-([0-9][0-9])-([0-9][0-9])T([0-9][0-9])[0-9][0-9])[0-9][0-9])(\.[0-9][0-9])?Z/i', $date, $matches))   

{       

if (isset($matches[7]) && substr($matches[7], 1) >= 50)           

    $matches[6]++;       

return strtotime("$matches[1]-$matches[2]-$matches[3] $matches[4]:$matches[5]:$matches[6] -0000");   

}   

else if (preg_match('/([0-9]{2,4})-([0-9][0-9])-([0-9][0-9])T([0-9][0-9])[0-9][0-9])[0-9][0-9])(\.[0-9][0-9])?(\+|-)([0-9][0-9])[0-9][0-9])/i', $date, $matches))   

{       

if (isset($matches[7]) && substr($matches[7], 1) >= 50)           

    $matches[6]++;       

return strtotime("$matches[1]-$matches[2]-$matches[3] $matches[4]:$matches[5]:$matches[6] $matches[8]$matches[9]$matches[10]");   

}   

else   

{       

return strtotime($date);   

}

}

?>

 

 

 

 

<?php

while ($result = mssql_fetch_array($doGet))

{

$date = $result['publish_date'];

$newdate = parse_date($date);

?>

 

 

<entry>

  <content><![CDATA[ %$£"!"£$]]></content>

  <title><?php echo $result['title']; echo ' '; echo '[Article Type: '; echo $result['mime_type']; echo ']'; echo ' [Article Rating: ' ; echo $result['exposure_impact']; echo ']'; echo ' [Theme: ' ; echo $result['theme_name']; echo ']'; ?> </title>

  <id><?php echo $result['url'];?></id>

  <updated><?php  echo $newdate;?></updated>

  <link><?php echo $result['url'];?><link>

  <summary><?php echo $result['summary'];?></summary>

</entry>

               

<?php

}

?>

 

</feed>

 

 

[craygo]

use mysql to format the date

 

SELECT DATE_FORMAT([datefield], '%Y-%m-%dT%k:%i:%sZ') as atomdate

 

replace [datefield] with your date field name

Ray

[craigy72]

Tried that, and I got:

 

'DATE_FORMAT' is not a recognized built-in function name.

 

I'm using MS SQL so I guess thats where the problem lies. I wonder if there is an equivalent...

[craigy72]

CONVERT (varchar,publish_date,126) AS atomdate - works

 

The only issue I have is that I need the atomdate to COALESCE with atom_createdate.

[craigy72]

Actually, no it's not, it's still not in the right format. Anyone got anymore ideas? I get this error in the feed validator

 

Sorry

 

This feed does not validate.

 

    *

 

      line 23, column 33: updated must be an RFC-3339 date-time: 2008-05-01 12:28:14 [help]

 

            <updated>2008-05-01 12:28:14</updated>