homepage Welcome to WebmasterWorld Guest from 54.204.79.235
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / RSS, ATOM, and Related Technologies
Forum Library, Charter, Moderators: bill & werty

RSS, ATOM, and Related Technologies Forum

    
RSS and MYSQL
How can I get RSS on my site
kingdean




msg:1543346
 12:03 am on Oct 7, 2005 (gmt 0)

Is there any good pages that explain how to make an RSS feed using a MYSQL database and or Navicat?

If I see an example of a working page that contacts a database I am sure I can figure it out or fill in my specifics. Does anyone here have any good leads on this?

Thanks
Dean

 

lupatis




msg:1543347
 6:30 pm on Oct 9, 2005 (gmt 0)

Hi There,

This may or may not help you - it is code from a blog script I wrote recently. It generates an XML feed by reading a MYSQL DB and then writing an XML file to the filesystem. Works for me. You will need to tidy up the XML Headers as they are not exactly well thought out, easy changes though.

I use this (modified) to generate feeds from all sorts of SQL Data (New Links, Blogs, News, Forum Posts etc) - very easy to do.

If you use this then write your post to your DB as usual and then include this code to overwrite the XML file. The file is only updated once per DB insert rather than each time the 'post' is viewed.

I'm a newbie, so if this code is off mark let me know ;-)

<?
//Setup your XML Headers
//refer to the RSS2 Spec at [rss-specifications.com...]
// This is develoment Code only - you will need to change some headers for your app ;)

//OUTPUT file. Make sure your PHP can write to this file/directory
$file = "/path/to/your/xml_feed/index.xml";

//Feed Meta Data

$feed_name = "";
$feed_link = "";
$feed_desc = "";
$feed_image_url = "";
$feed_image_text = "2;
$feed_image_wd = "200";
$feed_image_ht = "31";
$feed_image_link = "http://....";
$item_base_path = "http://....";
$copyright = "";
$date_build = date("r");

//Generate RSS Headers - Channel Info

$xml_headers = "<?xml version=\"1.0\"?>\n";
$xml_headers .= "<rss version=\"2.0\">\n";
$xml_headers .= "<channel>\n";
$xml_headers .= "<title>$feed_name</title>\n";
$xml_headers .= "<link>$feed_link</link>\n";
$xml_headers .= "<description>$feed_desc</description>\n";
$xml_headers .= "<language>en-gb</language>\n";
$xml_headers .= "<copyright>$copyright</copyright>\n";
$xml_headers .= "<lastBuildDate>$date_build</lastBuildDate>\n";
$xml_headers .= "<generator>PHP5</generator>\n";
$xml_headers .= "<image>\n";
$xml_headers .= "<url>$feed_image_link</url>\n";
$xml_headers .= "<title>$feed_image_text</title>\n";
$xml_headers .= "<width>$feed_image_wd</width>\n";
$xml_headers .= "<height>$feed_image_ht</height>\n";
$xml_headers .= "<link>$feed_image_link</link>\n";
$xml_headers .= "</image>\n";

//Connect to your Database

//Run Query

$resultxml = mysql_query("select * from TABLE WHERE id='$id' ORDER BY 'date' DESC $limit") or die("Error Message");
while ($r = mysql_fetch_array ($resultxml))
{
//Loop through array of rows and assign plain variables

//$id = $r[id];
$date = $r[date];
$user_id = $r[user_id];
$blog_id = $r[blog_id];
$title = $r[title];
$text = $r[text];
$link_name = $r[link_name];
$link_url = $r[link_url];
$img_name = $r[img_name];
$text = strip_tags($text);
$text = htmlentities($text);
$date = date("l jS F Y \(g:s a\)",$date);
$pub_date = date("r");
$item_link = $item_base_path;

//Generate Items

$xml_item .= "<item>\n";
$xml_item .= "<title>$title</title>\n";
$xml_item .= "<description><![CDATA[$text]]></description>\n";
//$xml_item .= "<comments>$feed_link</comments>\n";
$xml_item .= "<pubDate>$pub_date</pubDate>\n";
$xml_item .= "<guid isPermaLink=\"true\">".$item_base_path.$r[id]."</guid>\n";
$xml_item .= "<link>".$item_base_path."#".$r[id]."</link>\n";
$xml_item .= "</item>\n";

}

//Close off the RSS / XML file

$xml_footers .= "</channel>\n";
$xml_footers .= "</rss>";

//Make VAR out of Heads, Items and Footers

$xml_out = $xml_headers.$xml_item.$xml_footers;

//Write XML to file or send to browser as XML. I elect to write the file - easier on the server and more compatable with hosting.

/*
header("Content-Type: text/xml");
print $xml_out;
*/

//OR Write XML File - you may need to cange this to suit your app.

//You may want to change the error handling here too

if(is_writable($file)){
if(!$handle = fopen($file,'w')){
echo "Error on Opening XML File";
exit();
}

if(fwrite($handle, $xml_out) === FALSE){
echo "Error on Writing XML File";
exit();
}

//echo "XML File Written";
fclose($handle);
}else{
echo "Check Permissions: $file";
}
?>

bbcarter




msg:1543348
 6:27 pm on Oct 10, 2005 (gmt 0)

The easier answer is to use some of the programs out there for sale that use PHP to deliver RSS into html pages... can't mention what they are on WebmasterWorld, but try searching for
rss php seo html

etc.

lupatis




msg:1543349
 5:44 am on Oct 11, 2005 (gmt 0)

Hi BB,

Good point but I read the question as how to create RSS from SQL not how to display HTML from RSS, maybe I misunderstood? ;)

lupatis




msg:1543350
 5:46 am on Oct 11, 2005 (gmt 0)

Just noticed ..

line 14
$feed_image_text = "2;

should be

$feed_image_text = "";

experienced




msg:1543351
 3:11 pm on Oct 28, 2005 (gmt 0)

does RSS help in ranking for static sites also. How can we use this for static sites. New to RSS so pls..

Rgds
Exp..

trillianjedi




msg:1543352
 3:29 pm on Oct 28, 2005 (gmt 0)

Here's another one (my first bit of php code this if I remember right, so errors and omissions excepted, but it does work ;-)).

I made it cater for RSS, RSS 2.0 and ATOM formats. You can call which one you want with a?markup=atom or?markup=rss2 etc.

I've obviously removed specifics, but it should be pretty self-explanatory. Essentially, you have a loop and insert your data in XML format.

<?php

//Version 1.1
//Added RSS2 and ATOM support

//Get markup type variable and default to rss 0.91 if none
$markup = $_GET['markup'];
$lastupdated = 0;

if ($markup == "")
{
$markup = "rss";
}

$dbconn =& pnDBGetConn(true);
$pntable =& pnDBGetTables();

header("Content-Type: text/xml");

$sql_query = "insert your query here";

$result =& $dbconn->Execute($sql_query);

// Error checking
if ($dbconn->ErrorNo()!= 0)
{
return false;
}

if ($markup == "atom")
{
$lastupdated = $result->fields[5];
$stringArray = explode("-", $lastupdated);
$date = mktime(0,0,0,$stringArray[1],$stringArray[2],$stringArray[0]);
$partone = date("Y-m-d", $date);
$parttwo = date("H:i:s", $date);
$lastupdated = $partone."T".$parttwo."+00:00";
}

// start the RSS output

switch ($markup)
{
case "rss":
echo "<?xml version=\"1.0\" encoding=\"ISO-8859-1\"?>\n\n";
echo " <rss version=\"0.91\">\n\n";
echo "<channel>\n";
echo "<title>My Title</title>";
echo "<link>http://www.example.com</link>";
echo "<description>My super duper feed</description>";
echo "<language>en-gb</language>";
break;

case "rss2":
echo "<?xml version=\"1.0\" encoding=\"ISO-8859-1\"?>\n\n";
echo "<rss version=\"2.0\" xmlns:dc=\"http://purl.org/dc/elements/1.1/\">\n\n";
echo "<channel>\n";
echo "<title>My Title</title>";
echo "<link>http://www.example.com</link>";
echo "<description>My super duper feed</description>";
echo "<language>en-gb</language>";
break;

case "atom":
echo "<?xml version=\"1.0\" encoding=\"ISO-8859-1\"?>\n\n";
echo "<feed version=\"0.3\" xml:lang=\"en-GB\" xmlns=\"http://purl.org/atom/ns#\">";
echo "<title mode=\"escaped\" type=\"text/html\">My Title</title>";
echo "<generator url=\"http://www.example.com/\" version=\"1.01\">TrillianJedi</generator>";
echo "<link rel=\"alternate\" type=\"text/html\" href=\"http://www.example.com/\"/>";

if ($lastupdated <> 0)
{
echo "<modified>".$lastupdated."</modified>";
}

echo "<author><name>My name</name></author>";
break;
}

// start of dynamic XML part

for (;!$result->EOF; $result->MoveNext() )
{
$link = "http://www.example.com/".$result->fields[0];
$title = $result->fields[1];
$content = $result->fields[2];
$poster = $result->fields[4];
$datestamp = $result->fields[5];
$stringArray = explode("-", $datestamp);
$date = mktime(0,0,0,$stringArray[1],$stringArray[2],$stringArray[0]);

if ($markup == "atom")
{
//atom requires ISO 8601 date
$partone = date("Y-m-d", $date);
$parttwo = date("H:i:s", $date);
$updated = $partone."T".$parttwo."+00:00";
//$datestamp; //("Y-m-dTH:i:s+00:00", $date);
}

$converteddate = date("Y-m-d", $date);
$date = $converteddate;

if ($markup == "rss" ¦ $markup == "rss2")
{
echo "<item>\n";
echo "<title>$title</title>\n";
echo "<link>$link</link>\n";
echo "<description>$content</description>\n";

if ($markup == "rss2")
{
echo "<dc:creator>".$poster."</dc:creator>";
echo "<dc:date>".$date."</dc:date>";
}

echo "</item>\n";
}

if ($markup == "atom")
{
echo "<entry>";
echo " <title>$title</title>\n";
echo " <link rel=\"alternate\" title=\"".$title."\" type=\"text/html\" href=\"".$link."\" />";
//echo " <id></id>";
echo "<id>tag:example.com,2005:link-".$result->fields[0]."</id>";
echo "<issued>".$updated."</issued>";
echo " <modified>".$updated."</modified>";
echo " <summary>$content</summary>\n";
echo "</entry>";
}
}
// end of dynamic output

if ($markup == "rss" ¦ $markup == "rss2")
{
echo "</channel>\n";
echo "</rss>\n";
}

if ($markup == "atom")
{
echo "</feed>";
}

?>

TJ

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / RSS, ATOM, and Related Technologies
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved