SQL+PHP Generating an RSS/XML Feed

[ReeceSayer]

Hi,

 

I've written some code to take information from an SQL database and write it out in the RSS format (Although it doesn't validate).

 

The problem is i'd like the page to have the .rss (or .xml) file extension, I'm not sure if there's any advantages in having this but thought i'd ask.

 

I've got the following code:

 

<?php 
header('Content-type: text/xml');
print '<?xml version="1.0"?>';
print '<rss version="2.0">';
        print '<channel>'; 
	include("phpfunctions.php");
	db_connect();

	//select all from users table
	$select="SELECT title, link, description FROM news"; 
	$result = mysql_query($select) or die(mysql_error());

	//If nothing is returned display error no records
	if (mysql_num_rows($result) < 1) { 
	die("No records"); 
	} 

//loop through the results and write each as a new item

while ($row = mysql_fetch_assoc($result)) { 
    $item_title = $row["title"]; 
    $item_link = $row["link"]; 
    $item_desc = $row["description"]; 

        print '<item>'; 
           print '<title>' . $item_title . '</title>'; 
            print '<link>' . $item_link . '</link>'; 
            print '<description>' . $item_desc . '</description>'; 
        print '</item>'; 


}
print '</channel>';
print '</rss>';

?>

 

This seems to work fine as i get what i expect and i'm assuming i can do the same to output .xml but is there a way to have it in a proper .rss / .xml file so that an aggregator or someone could read this properly.

 

Cheers,

 

Reece

[xyph]

PHP has a built in solution for this! SimpleXML!

 

Here's a quick example. Take this and apply it to what you're doing.

<?php

// This is the shell of our XML file that we will 'feed' to SimpleXML
$xml_shell = '<?xml version="1.0"?><grandparent></grandparent>';

// $xml will hold an instance of the 'SimpleXMLElement'
// class - a larger, more abstract 'function' with internal
// variables(properties) and functions(methods)
$xml_grandparent = new SimpleXMLElement( $xml_shell );

// This is a placeholder for the MySQL data you're getting
$rows = array(
array( 'column1' => 'foo', 'column2' => 'bar' ),
array( 'column1' => 'hello', 'column2' => 'world' ),
array( 'column1' => 'homer', 'column2' => 'simpson' ),
array( 'column1' => 'mysql', 'column2' => 'data' )
);

// This is the 'array equivalent' of while( $row = mysql_fetch_whatever($result) )
foreach( $rows as $row ) {
// First we want to create a child of <grandparent>, called <parent>
// Because we will want to later add <children> to that <parent>, we'll store
// it in a variable we can use later
// SimpleXMLElement has a method called addChild, that RETURNS it's own 'linked'
// SimpleXMLElement instance that can have children as well
$xml_parent = $xml_grandparent->addChild( 'parent' );
// Now we can loop through each MySQL column with a foreach, and populate
// <parent> with some children
foreach( $row as $column => $value ) {
	$xml_parent->addChild( $column, $value );
}

}

// Because all the instances are linked, we only have to echo the initial instance
// to output the entire family.
// First, we tell the browser we're echo'ing an XML file using the header command
// I use the UTF-8 characterset, so I specify that as well
header( 'Content-type: text/xml; charset=UTF-8' ); 

// Now we simply use another method of SimpleXMLElement to echo out the XML
echo $xml_grandparent->asXML();

// More on SimpleXML
// http://php.net/manual/en/book.simplexml.php

?>

[ReeceSayer]

Brilliant cheers,

 

I did google it but it just came up with a bunch of tutorials for basically what i'd already done.

 

I'll mark as solved.

 

Thanks

[ReeceSayer]

Apparently it's not solved.

 

I went straight in made the changes and made put them live but i get an internal server error.

 

So to double check i put the file in xampp and it works perfectly.

 

So is there anything i need to turn on in my php config to allow me to do this?

 

I've only had a live host for a couple of months so i'm not too sure.

 

Cheers

 

 

Brilliant cheers,

 

I did google it but it just came up with a bunch of tutorials for basically what i'd already done.

 

I'll mark as solved.

 

Thanks

[xyph]

It's hard to say without seeing the error.

 

Internal server error usually has to do with the web server, and not PHP. It could be a bad .htaccess file, or something.

[ReeceSayer]

Hi again,

 

I've just been on the phone to hostgator tech support and they said that he had to do a file permissions re-write for my site (files: 644 folders:755), this solved the issue, i've seen quite a dew of these that seem to be unresolved on the forum so thought i'd post the solution.

 

I have another problem with simpleXML but if i need help i'll post it as a new topic later.

 

Reece

 

EDIT: Sorry xyph i was writing this as you'd posted. Cheers

[gizmola]

Hostgator must have some pretty shitty server configuration if the lack of read/execute perms on a php script cause a 500 error.

[xyph]

I dunno, look at that attractive female with a headset in the top right just waiting to talk to us about hosting

[ReeceSayer]

Yeah, it's definitely not the best, took them about 15mins to sort out the issue too.

 

I've managed to sort out the code to write to an xml file:

 

<?php
$xml_shell = '<?xml version="1.0"?><?xml-stylesheet type="text/xsl" href="style.xlst"?><grandparent></grandparent>';

include("phpfunctions.php");
db_connect();

$xml_grandparent = new SimpleXMLElement( $xml_shell );

	//select all from users table
	$select="SELECT title, link, description FROM news"; 
	$result = mysql_query($select) or die(mysql_error());

while( $row = mysql_fetch_assoc($result) ) {

$xml_parent = $xml_grandparent->addChild( 'parent' );

foreach( $row as $column => $value ) {
	$xml_parent->addChild( $column, $value );
}

}

header( 'Content-type: text/xml; charset=UTF-8' ); 

// write the xml out to a document
fwrite( $xml_grandparent->asXML("news.xml"));
?>

 

Just wondering how i'd do a properly formed RSS, i could replace grandparent with channel and parent with item... but still not sure how i'd go about getting it into an rss file.

 

Cheers

[xyph]

You'll only need to follow the RSS spec in formatting your XML.

 

You don't need to have a certain extension to have a valid RSS file - The Content-type header tells whoever is accessing this page to expect XML content. If you open the example up in your browser, it will display just like an XML file would

 

[xyph]

My mistake, you probably want 'application/xml'

 

http://www.imc.org/ietf-xml-mime/mail-archive/msg00496.html

[gizmola]

The last time I had to do this, rather than reinventing the wheel, I just used Zend Framework.  One of the advantages of their code is that you can setup one list and it will output multiple formats, so you can offer rss and atom with the same code.  Take a look at this thread:  http://www.phpfreaks.com/forums/index.php?topic=320176.msg1516021#msg1516021

[ReeceSayer]

Cheers,

 

I'll give first one a go before attempting the zend solution as i've never used a framework before.

 

Thanks again.