esnet_code

so this is what i have to generate a rss feed from several tables worth of info. basically it joins each row of each table together. it would be terrific if it would order everything by date- but my tables are currently not formatted in a way to join them in a coherent way. i'm slowly getting all the dates formatted in a way that is translatable- which seems to be in a month/day/year hr:min am/pm format (6/23/2009 2:46pm). one loop goes 10 times through each table. in each loop the newest (or next) news, dump, blog, movies, music, and books gets an xml item. this is obviously a php file, so i've got mod_rewrite handling the url request with something like this:


RewriteRule ^(rss.rss)$		/rss/rss.php [L]
RewriteRule ^(rss.xml)$		/rss/rss.php [L]
RewriteRule ^(rss/)$		/rss/rss.php [L]

see the output here


<?php

function cleanText($intext) {

   $ret= str_replace("&", "&amp;",str_replace("\r\n"," ",substr(strip_tags($intext), 0, 150)));


    return $ret;

}

header("Content-Type: text/xml;charset=utf-8");

//include db log/pass

require("hackpwd.inc");



mysql_connect($server, $dbuser, $dbpass);






// store items from the database in the $result1 array

mysql_select_db("db");




$newsQuery = "SELECT * FROM news ORDER BY id DESC LIMIT 3";

$newsResult = mysql_query($newsQuery);




$dumpQuery = "SELECT * FROM dump ORDER BY id DESC LIMIT 3";

$dumpResult = mysql_query($dumpQuery);




$blogQuery = "SELECT * FROM blog ORDER BY id DESC LIMIT 3";

$blogResult = mysql_query($blogQuery);




$moviesQuery = "SELECT * FROM movies ORDER BY id DESC LIMIT 3";

$moviesResult = mysql_query($moviesQuery);




$musicQuery = "SELECT * FROM music ORDER BY id DESC LIMIT 3";

$musicResult = mysql_query($musicQuery);




$booksQuery = "SELECT * FROM books ORDER BY id DESC LIMIT 3";

$booksResult = mysql_query($booksQuery);






echo'<?xml version="1.0" encoding="utf-8"?>

<rss version="2.0">

   <channel>

      <title>edwardsharp.net</title>


      <link>http://www.edwardsharp.net</link>

      <description>a network of wonderful things.</description>

      <language>en-us</language>


      <docs>http://edwardsharp.net/rss</docs>'; 



for ($i = 1; $i <= 3; $i++) {


    $newsRow= mysql_fetch_array($newsResult, MYSQL_ASSOC);

    $newsTitle = 'esnet_news_'. $newsRow["date"];

    $newsDescription = cleanText($newsRow["data"]); 


    $newsDate = date("r", strtotime($newsRow["date"]));

    $newsLink = "http://www.edwardsharp.net/news/#news".$newsRow["id"];




    $dumpRow=mysql_fetch_array($dumpResult, MYSQL_ASSOC);

    $dumpTitle = 'esnet_dump_'.$dumpRow["date"];


    $dumpDescription = cleanText($dumpRow["data"]); 

    $dumpDate = date("r", strtotime($dumpRow["date"]));


    $dumpLink = "http://www.edwardsharp.net".$i."b";



    $blogRow=mysql_fetch_array($blogResult, MYSQL_ASSOC);


    $blogTitle = 'esnet_writing_'.$blogRow["title"];  

    $blogDescription = cleanText($blogRow["data"]); 


    $blogDate = date("r", strtotime($blogRow["date"]));    

    $blogLink = "http://www.edwardsharp.net/esharp/writing/#writing".$blogRow["id"];




    $moviesRow=mysql_fetch_array($moviesResult, MYSQL_ASSOC);

    $moviesTitle = 'esnet_movies_'.$moviesRow["data"];


    $moviesDescription = 'currently watching: '.cleanText($moviesRow["name"]).' by: '.cleanText($moviesRow["director"]); 


    $moviesDate = date("r", strtotime($moviesRow["data"]));

    $moviesLink = "http://www.edwardsharp.net".$i."d";




    $musicRow=mysql_fetch_array($musicResult, MYSQL_ASSOC);

    $musicTitle = 'esnet_music_'.$musicRow["data"];   


    $musicDescription = 'currently listening to: '.cleanText($musicRow["album"]).' by: '.cleanText($musicRow["artist"]); 


    $musicDate = date("r", strtotime($musicRow["data"]));

    $musicLink = "http://www.edwardsharp.net".$i."e";




    $booksRow=mysql_fetch_array($booksResult, MYSQL_ASSOC);

    $booksTitle = 'esnet_books_'.$booksRow["data"];


    $booksDescription = 'currently reading: '.cleanText($booksRow["name"]).' by: '.cleanText($booksRow["author"]); 


    $booksDate = date("r", strtotime($booksRow["data"]));

    $booksLink = "http://www.edwardsharp.net".$i."f";




$link="http://www.edwardsharp.net";

// display an item

ECHO <<<END

    <item>

         <title>$newsTitle</title>


         <link>$newsLink</link>

         <description>$newsDescription</description>

         <pubDate>$newsDate</pubDate>

         <guid>$newsLink</guid>

    </item>


    <item>

         <title>$dumpTitle</title>

         <link>$link</link>

         <description>$dumpDescription</description>

         <pubDate>$dumpDate</pubDate>


         <guid>$dumpLink</guid>

    </item>

    <item>

         <title>$blogTitle</title>

         <link>$blogLink</link>

         <description>$blogDescription</description>


         <pubDate>$blogDate</pubDate>

         <guid>$blogLink</guid>

    </item>

    <item>

         <title>$moviesTitle</title>

         <link>$link</link>


         <description>$moviesDescription</description>

         <pubDate>$moviesDate</pubDate>

         <guid>$moviesLink</guid>

    </item>

    <item>

         <title>$musicTitle</title>


         <link>$link</link>

         <description>$musicDescription</description>

         <pubDate>$musicDate</pubDate>

         <guid>$musicLink</guid>

    </item>


    <item>

         <title>$booksTitle</title>

         <link>$link</link>

         <description>$booksDescription</description>

         <pubDate>$booksDate</pubDate>


         <guid>$booksLink</guid>

    </item>

END;



}



ECHO <<<END



   </channel>

</rss>


END;



?>