Welcome to WebmasterWorld Guest from 54.147.217.76

Forum Moderators: httpwebwitch

Message Too Old, No Replies

How to generate large no. of xml feed files from DB

   
12:05 pm on Mar 17, 2009 (gmt 0)

5+ Year Member



I have thousands of product records in my DB and each product record has its own html page.
I want to create an xml rss file for each record, but i am getting some wanings with only 400 rss files were generated.

I get no error or any warning when i create only 100 xml files.

Please help me how can I generate these rss files for all of my products.

thanx.

3:07 pm on Mar 17, 2009 (gmt 0)

WebmasterWorld Administrator httpwebwitch is a WebmasterWorld Top Contributor of All Time 10+ Year Member



welcome to WebmasterWorld!

In your situation, I would use a server-side scripting language to generate the feeds dynamically, on demand. Since you already have them in a database, grabbing the information, wrapping it in XML, and delivering it over the web is a breeze.

For instance, if I request a URL like
http://example.com/feed.php?productid=12345

then my script would output XML describing product 12345.

Is your server capable of server scripting? There are many flavours: PHP, C#, Java, ColdFusion, Perl, Ruby... etc

5:20 am on Mar 18, 2009 (gmt 0)

5+ Year Member



Yes my server is capable of server scripting and I am using php.
6:27 am on Mar 18, 2009 (gmt 0)

5+ Year Member



Perfect. You just need to take the query string as shown in the example by httpwebwitch, evaluate the value and pass it to a SELECT statement that retrieves that record from your database. Then wrap the resultset in XML, making sure you force the Content-Type header as a valid XML or RSS type before you output the string.
6:44 am on Mar 18, 2009 (gmt 0)

5+ Year Member



where is query example by httpwebwitch.
6:58 am on Mar 18, 2009 (gmt 0)

5+ Year Member



http://example.com/feed.php?productid=12345

That's the query string I mentioned. It's a name : value pair that can be evaluated by your receiving PHP script as part of the $_GET superglobal. So in this case, $_GET['productid'] would contain the value 12345. Take that attribute, and after filtering and checking it of course, use the value in your SQL SELECT statement to retrieve the record WHERE id={value}.

3:18 pm on Mar 18, 2009 (gmt 0)

WebmasterWorld Administrator httpwebwitch is a WebmasterWorld Top Contributor of All Time 10+ Year Member



a simple example:

<?php
mysql_connect [ca.php.net]( your db credentials go here )
header [ca.php.net]("content-type: text/xml");
$id = $_GET['id'];
$xmloutput = "";
$sqlquery = "SELECT * FROM your_table WHERE id = ".$id;
$result = mysql_query [ca.php.net]($sqlquery);
$xmloutput .= "<?xml version=\"1.0\"?>";
$xmloutput .= "<root>";
while($row = mysql_fetch_array [ca.php.net]($result)){
$xmloutput .= "<node>". htmlspecialchars [ca.php.net]($row['your_column_name']) ."</node>";
}
$xmloutput .= "</root>";
echo $xmloutput;
?>

Use PHP to build your XML as a string. When the xml is complete, output it using echo or print().

Note the header() command, which gives the HTTP response an XML mimetype, and the <?xml ?> declaration itself which is part of the document.

Get this working first. Then you'll be able to retrieve XML via a URL like this:
http://example.com/myscript.php?id=12345
Because of that header, it will return a bona fide XML Document, and will be delivered just like it was a real *.xml file sitting on your server.

What I'd do after that is use a little Apache rewriting magic to map that script to a regular expression pattern, so I could get the same XML by requesting this:
http://example.com/12345.xml

6:38 pm on Mar 18, 2009 (gmt 0)

5+ Year Member



Nice followup and example, httpwebwitch. The only additional thing I would be sure to recommend is error handling. Make certain the query string is what it's supposed to be, and if either the string or the SQL statement fail, let them fail gracefully with an error message embedded as a node in the XML. The JavaScript / XSL / PHP / whatever that expects this XML document can look for the <errormsg /> node and act accordingly.
12:19 am on Mar 19, 2009 (gmt 0)

WebmasterWorld Administrator httpwebwitch is a WebmasterWorld Top Contributor of All Time 10+ Year Member



if it's a query-based API, then an XML document with an error message in it is appropriate. But if you're merely republishing static data in an XML feed, then for an invalid request an empty response with a 404 HTTP status is also OK. The former is more friendly for the client who consumes the data, the latter is easier to build.

there are so many choices

When I'm building an XML feed or API, I look at some of the excellent products available from Yahoo as examples of "doing it right". That's not an endorsement of their services per se; it's a recommendation if you want to see XML handled and delivered expertly.

All of which can be done with a little SQL and PHP, in a hundred lines or less