Web Analytics Made Easy -
StatCounter Problem Validating an RSS feed - CodingForum

Problem Validating an RSS feed

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts
  • Crowds
    CodingForum Regular
    • May 2005
    • 251

    Problem Validating an RSS feed

    I have created a feed from a blog script I am working on and have managed to get it working. But it just wont validate. Its the <pubdate/> thats giving me gip.

    The feed content is fetched from mysql but the date stored in mysql is not a RFC-822 date time. So I need to convert it to RFC-822 some how.
    I did read something here http://www.tech-recipes.com/mysql_tips1461.html about it which says I need to do the following mysql query

    PHP Code:
    SELECT DATE_FORMAT(pubdate,'%a, %d %b %Y %T') AS rfcpubdate FROM tablename WHERE 1 

    echo "<pubDate>$rfcpubdate ".date('T')."</pubDate>"
    But I am unsure how to include this into my existing query without messing up my while loop.

    What I have so far
    PHP Code:
    <? header('Content-type: text/xml'); 
    include_once 
    '../Connections/CDblog.php'?>


    <rss version="2.0">
    <channel>
    <title><? echo $btitle ?>News Feed</title>
    <description>Latest News From <? echo $btitle ?></description>
    <link><? echo $site ?></link>
    <copyright><? echo $copy ?></copyright>

    <?
    mysql_connect
    ($hostname_CDblog,$username_CDblog,$password_CDblog);
    mysql_select_db($database_CDblog) or die( "Unable to select database");

    $q="SELECT * FROM CDblg_article_art ORDER BY id_art DESC LIMIT 2";
    $doGet=mysql_query($q);

    while(
    $result mysql_fetch_array($doGet)){
    ?>
         <item>
            <title> <?=htmlentities(strip_tags($result['title_art'])); ?></title>
            <description> <?=htmlentities(strip_tags($result['text_art'],'ENT_QUOTES'));?></description>
            <link><? echo $site ?>article.php?id_art=<?=$result['id_art'];?></link>
            <guid><? echo $site ?>article.php?id_art=<?=$result['id_art'];?></guid>
            <pubDate> <?=strftime"%a, %d %b %Y %T %Z" $result['date_art']); ?></pubDate>
            
         </item>  
    <? ?>  

    </channel>
    </rss>
    Any ideas ?

    Crowds
    PHP magpie | And President Of The Marmalade Atkins Fan Club | Crowds Design. Member of Chesterfield Bespoke cakes @ Le Petit Gateau
  • raf
    Major Contributer
    • Jul 2002
    • 6806

    #2
    change
    $q="SELECT * FROM CDblg_article_art ORDER BY id_art DESC LIMIT 2";
    into
    $q="SELECT title_art, text_art, id_art, DATE_FORMAT(date_art,'%a, %d %b %Y %T') AS rfc_date FROM CDblg_article_art ORDER BY id_art DESC LIMIT 2";


    and change
    <pubDate> <?=strftime( "%a, %d %b %Y %T %Z" , $result['date_art']); ?></pubDate>
    into
    <pubDate><?=$result['rfc_date'];?></pubDate>

    i didn't check if the date_format() that you posted is correct...
    btw: you shourld better use <?php instead of <? and not drop in and out php-mode so much...
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

    Comment

    • Crowds
      CodingForum Regular
      • May 2005
      • 251

      #3
      Thanks raf, I will try that in a mo. Out of intrest what problems can tbe caused by not having the <?php prefix and dropping in and out of php ?
      PHP magpie | And President Of The Marmalade Atkins Fan Club | Crowds Design. Member of Chesterfield Bespoke cakes @ Le Petit Gateau

      Comment

      • Crowds
        CodingForum Regular
        • May 2005
        • 251

        #4
        Hmmm its still not validating. To make maters worse it is not retriveing pubdate information from mysql now

        Crowds
        PHP magpie | And President Of The Marmalade Atkins Fan Club | Crowds Design. Member of Chesterfield Bespoke cakes @ Le Petit Gateau

        Comment

        • raf
          Major Contributer
          • Jul 2002
          • 6806

          #5
          Originally posted by Crowds View Post
          Thanks raf, I will try that in a mo. Out of intrest what problems can tbe caused by not having the <?php prefix and dropping in and out of php ?
          using <? will only work if shorttags are enabled, so you better not rely on it.
          dropping in and out of php-mode has a slight performance loss, but it's also harder to read the code.
          To make maters worse it is not retriveing pubdate information from mysql now
          meaning?
          Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

          Comment

          • Crowds
            CodingForum Regular
            • May 2005
            • 251

            #6
            Cheers raf,

            It is failing to retrive the information from the mysql table date_art. There are no mysql errors reported its just that the information from date_art now isnt displayed on any test feed.
            The feed can be found here...


            As you can see it is also just displaying the colsing </pubdate> but not the <pubdate>
            Which is something that happens (i think) when there is no content. same as the </copyright> which I have left blank myself.
            Cheers
            Crowds
            Last edited by Crowds; Oct 5, 2006, 01:42 PM.
            PHP magpie | And President Of The Marmalade Atkins Fan Club | Crowds Design. Member of Chesterfield Bespoke cakes @ Le Petit Gateau

            Comment

            • raf
              Major Contributer
              • Jul 2002
              • 6806

              #7
              change your code to
              PHP Code:
              <?php
              $q
              ="SELECT title_art, text_art, id_art, DATE_FORMAT(date_art,'%a, %d %b %Y %T') AS rfc_date FROM CDblg_article_art ORDER BY id_art DESC LIMIT 2";
              echo 
              '<br />debug:'$q;
              $doGet=mysql_query($q);

              while(
              $result mysql_fetch_array($doGet)){
                 echo 
              '<br />debug:';
                 
              print_r($result);
                 echo 
              '<item>
                      <title>'
              htmlentities(strip_tags($result['title_art'])), '</title>
                      <description> '
              htmlentities(strip_tags($result['text_art'],'ENT_QUOTES')) ,'</description>
                      <link>'
              $site ,'article.php?id_art='$result['id_art'] ,'</link>
                      <guid>'
              $site ,'article.php?id_art='$result['id_art'] ,'</guid>
                      <pubDate>'
              ,  $result['rfc_date'] ,'</pubDate>
                      </item>'
              ;  

              ?>
              and then post the output.

              you could also try to run the sql-command in a db-frontend and see what results it then turns out.
              Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

              Comment

              • Crowds
                CodingForum Regular
                • May 2005
                • 251

                #8
                This is what I get..
                Code:
                −
                	<rss version="2.0">
                −
                	<channel>
                <title>CDblog TestNews Feed</title>
                <description>Latest News From CDblog Test</description>
                <link>http://crowds.slashdash.co.uk/CDblog/</link>
                <copyright/>
                <br/>
                debug:SELECT title_art, text_art, id_art, DATE_FORMAT(date_art,'%a, %d %b %Y %T') AS rfc_date FROM CDblg_article_art ORDER BY id_art DESC LIMIT 2
                <br/>
                debug:Array
                (
                    [0] => Welcome to CDblog
                    [title_art] => Welcome to CDblog
                    [1] => You can delete this from the admin panel after you have created your Blog Areas
                    [text_art] => You can delete this from the admin panel after you have created your Blog Areas
                    [2] => 1
                    [id_art] => 1
                    [3] => 
                    [rfc_date] => 
                )
                −
                	<item>
                <title>Welcome to CDblog</title>
                −
                	<description>
                 You can delete this from the admin panel after you have created your Blog Areas
                </description>
                −
                	<link>
                http://crowds.slashdash.co.uk/CDblog/article.php?id_art=1
                </link>
                −
                	<guid>
                http://crowds.slashdash.co.uk/CDblog/article.php?id_art=1
                </guid>
                <pubDate/>
                </item>
                </channel>
                </rss>
                As an sql query in phpMyadmin it shows the rfc_date field as null... I dont have a field rfc_date. But I guess it means date_art.

                If I browse the tables in myPhpadmin it shows the tables as not null...

                Cheers raf

                Crowds
                PHP magpie | And President Of The Marmalade Atkins Fan Club | Crowds Design. Member of Chesterfield Bespoke cakes @ Le Petit Gateau

                Comment

                • raf
                  Major Contributer
                  • Jul 2002
                  • 6806

                  #9
                  As an sql query in phpMyadmin it shows the rfc_date field as null... I dont have a field rfc_date. But I guess it means date_art.
                  in the resultset, you have a field rfc_date because that is the alias for the date_format'ed date_art field, so that's ok.

                  lets change the code to also show the original date. Change

                  $q="SELECT title_art, text_art, id_art, DATE_FORMAT(date_art,'%a, %d %b %Y %T') AS rfc_date FROM CDblg_article_art ORDER BY id_art DESC LIMIT 2";

                  into

                  $q="SELECT title_art, text_art, id_art, date_art, DATE_FORMAT(date_art,'%a, %d %b %Y %T') AS rfc_date FROM CDblg_article_art ORDER BY id_art DESC LIMIT 2";

                  and post the output of your script again.

                  also: what MySQL version are you running? and what is the columntype of date_art?
                  Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

                  Comment

                  • Crowds
                    CodingForum Regular
                    • May 2005
                    • 251

                    #10
                    Sorry for the late reply I have been away...
                    This is the output I now get....
                    Code:
                    −
                    	<rss version="2.0">
                    −
                    	<channel>
                    <title>CDblog_testNews Feed</title>
                    <description>Latest News From CDblog_test</description>
                    <link>http://crowds.slashdash.co.uk/CDblog/</link>
                    <copyright/>
                    <br/>
                    debug:SELECT title_art, text_art, id_art, date_art, DATE_FORMAT(date_art,'%a, %d %b %Y %T') AS rfc_date FROM CDblg_article_art ORDER BY id_art DESC LIMIT 2
                    <br/>
                    debug:Array
                    (
                        [0] => date time ?
                        [title_art] => date time ?
                        [1] => date time?
                        [text_art] => date time?
                        [2] => 2
                        [id_art] => 2
                        [3] => 2006-10-19 18:04:05
                        [date_art] => 2006-10-19 18:04:05
                        [4] => Thu, 19 Oct 2006 18:04:05
                        [rfc_date] => Thu, 19 Oct 2006 18:04:05
                    )
                    −
                    	<item>
                    <title>date time ?</title>
                    <description> date time?</description>
                    −
                    	<link>
                    http://crowds.slashdash.co.uk/CDblog/article.php?id_art=2
                    </link>
                    −
                    	<guid>
                    http://crowds.slashdash.co.uk/CDblog/article.php?id_art=2
                    </guid>
                    <pubDate>Thu, 19 Oct 2006 18:04:05</pubDate>
                    </item>
                    <br/>
                    debug:Array
                    (
                        [0] => Welcome to CDblog
                        [title_art] => Welcome to CDblog
                        [1] => You can delete this from the admin panel after you have created your Blog Areas
                        [text_art] => You can delete this from the admin panel after you have created your Blog Areas
                        [2] => 1
                        [id_art] => 1
                        [3] => 0000-00-00 00:00:00
                        [date_art] => 0000-00-00 00:00:00
                        [4] => 
                        [rfc_date] => 
                    )
                    −
                    	<item>
                    <title>Welcome to CDblog</title>
                    −
                    	<description>
                     You can delete this from the admin panel after you have created your Blog Areas
                    </description>
                    −
                    	<link>
                    http://crowds.slashdash.co.uk/CDblog/article.php?id_art=1
                    </link>
                    −
                    	<guid>
                    http://crowds.slashdash.co.uk/CDblog/article.php?id_art=1
                    </guid>
                    <pubDate/>
                    </item>
                    </channel>
                    </rss>
                    And the column type for date_art is datetime... should it be just date ?

                    Thanks again raf
                    PHP magpie | And President Of The Marmalade Atkins Fan Club | Crowds Design. Member of Chesterfield Bespoke cakes @ Le Petit Gateau

                    Comment

                    • raf
                      Major Contributer
                      • Jul 2002
                      • 6806

                      #11
                      i see 2 problems:
                      - for the second record the date_art is 0000-00-00 00:00:00 which is not a valid date --> you'll need to set another dummy/default vallue for that column
                      - i had a look at the RFC-822 date time specc, and the format should be like "Wed, 02 Oct 2002 15:00:00 +0200" so you need to add the timezone (the +0200 in the example) like
                      PHP Code:
                      echo '...
                      <pubDate>'
                      ,  $result['rfc_date'], ' 'date('O'),'</pubDate>
                      ...'

                      Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

                      Comment

                      • Crowds
                        CodingForum Regular
                        • May 2005
                        • 251

                        #12
                        Hey raf, big tick in the thank you box
                        I made the change to the the pubdate line and a few alterations in my sql and it now validates !
                        You have been (once again) a big help.

                        ----------

                        For anyone finding this via a search here's what I have that gets me a valid RSS 2 feed.
                        A php page that creates my tables

                        Code:
                        <?php 
                        
                        
                        
                        mysql_query("CREATE TABLE tablename (
                          col1 int(11) NOT NULL auto_increment,
                          col2 int(11) NOT NULL default '0',
                          col3 varchar(100) NOT NULL default '',
                          col4 varchar(255) NOT NULL default '',
                          col5 longtext NOT NULL,
                          date_art datetime default NULL,
                          PRIMARY KEY  (id_art))")or die(mysql_error());
                          
                        
                        // creates a default entry
                        // $date = date("Y-m-d H:i:s"); is important to get the right kind of
                        //datetime for the conversion as mentioned by raf
                        $date = date("Y-m-d H:i:s");
                        $query = "INSERT INTO tablename (col1, col2, col3, col4, col5, date_art) VALUES ('whatever', 'whatever', 'whatever', 'whatever', 'whatever', '$date')";
                        
                        
                        mysql_query($query) or die(mysql_error());
                        
                        ?>
                        And my feed.php page

                        PHP Code:
                        <? header('Content-type: text/xml'); 
                        include_once 
                        '../Connections/conn.php'?>


                        <rss version="2.0">
                        <channel>
                        <title><? echo $btitle ?>News Feed</title>
                        <description>Latest News From <? echo $btitle ?></description>
                        <link><? echo $site ?></link>
                        <copyright><? echo $copy ?></copyright>

                        <?
                        mysql_connect
                        ($host,$username,$password);
                        mysql_select_db($database) or die( "Unable to select database");

                        $q="SELECT col1, col2, col3, date_art, DATE_FORMAT(date_art,'%a, %d %b %Y %T') AS rfc_date FROM tablename ORDER BY id_art DESC LIMIT 2";


                        $doGet=mysql_query($q);

                        while(
                        $result mysql_fetch_array($doGet)){
                          
                           echo 
                        '<item>
                                <title>'
                        htmlentities(strip_tags($result['col1'])), '</title>
                                <description> '
                        htmlentities(strip_tags($result['col2'],'ENT_QUOTES')) ,'</description>
                                <link>'
                        $site ,'article.php?id_art='$result['col3'] ,'</link>
                                <guid>'
                        $site ,'article.php?id_art='$result['col3'] ,'</guid>
                                <pubDate>'
                        ,  $result['rfc_date'], ' 'date('O'),'</pubDate>
                                </item>'
                        ;  

                        ?>
                        </channel>
                        </rss>

                        It should be noted that for the folder containing the feed.php page has a .htaccess file in it with

                        Code:
                        AddType application/x-httpd-php .xml
                        Added to it. This makes any php page contained within the same folder be read as an xml doc. (only valid for linux servers)
                        ----------

                        Thanks agin raf !

                        Crowds
                        Last edited by Crowds; Oct 22, 2006, 09:30 AM. Reason: Update to fill in a missing quote.
                        PHP magpie | And President Of The Marmalade Atkins Fan Club | Crowds Design. Member of Chesterfield Bespoke cakes @ Le Petit Gateau

                        Comment

                        • raf
                          Major Contributer
                          • Jul 2002
                          • 6806

                          #13
                          you're welcome + thanks for posting your code uphere.

                          2 comments:
                          1) in the code to create the table and insert a record --> if you just want to insert the current datetime, then you should use the MySQL function Now(), like
                          PHP Code:
                          $query "INSERT INTO tablename (col1, col2, col3, col4, col5, date_art) VALUES ('whatever', 'whatever', 'whatever', 'whatever', 'whatever', Now())"
                          There are only 2 situations where i think it would be usefull to create the datetime value in PHP, and that's when
                          * the MySQL server runs on another machine in a different timezone (so it's clock is not synchonized with your webserver)
                          * you want to make thousands of inserts with the same datetime value (the now() could get another value during the inserts)

                          2) in feed.php, you're missing a quote in
                          PHP Code:
                          <link>', $site ,'article.php?id_art=', $result[col3'] ,'</link> 
                          should be
                          PHP Code:
                          <link>', $site ,'article.php?id_art=', $result['col3'] ,'</link
                          Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

                          Comment

                          • Crowds
                            CodingForum Regular
                            • May 2005
                            • 251

                            #14
                            Thanks raf,
                            I didnt know about the now() function. My mysql server is seperate to my webserver but I doubt there is any time difference between the two. I will change it accordingly.

                            I must of nuked the quote when I was getting rid of my Col names I have updated it now.

                            Thanks again raf.


                            Crowds
                            PHP magpie | And President Of The Marmalade Atkins Fan Club | Crowds Design. Member of Chesterfield Bespoke cakes @ Le Petit Gateau

                            Comment

                            Working...
                            X