Forum Moderators: open
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
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";
}
?>
etc.
Good point but I read the question as how to create RSS from SQL not how to display HTML from RSS, maybe I misunderstood? ;)
line 14
$feed_image_text = "2;
should be
$feed_image_text = "";
Rgds
Exp..
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
