[question] using XML to display data from database

[subhash]

Hi,

I am learning about XML, and had an idea that I had a question about. At work, I am redesigning their existing website, and they have a section where they display realtor listings for all the counties in different states where they do business.

Here is an example

[url:qgj6o3x5wp]http://www.accessloans.com/realtors_fl.html[/url:qgj6o3x5wp]

Right now, we are using tables to display each realtor separately, and the file gets pretty bulky. If we put all the realtors into an Access database, is there a way to use XML & XSL to make one definition of the format of how the listing should look (just as it does in the existing file), and just loop through, and insert in the page without such a big HTML file?

Thanks in advance for any help.



Subhash

[Mark]

In the end you'll always end up with a table. XSLT transform a XML file to whatever you want. In your case, an HTML file.

The table you have now could use a lot of optimization, though.

- [url="http://neo.dzygn.com/"]Mark[/url:zr02834mgu]

[simu]

That should be no problem... write a php or asp file, where you declare the content-type as "text/xml" and read out the db-entrys and print them in xml-format.

here's an example how to do this with php... it's a mysql db, but that should not be a problem:

[code:mvhtkkostv]
<?php

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

print "<?xml version=\"1.0\" encoding=\"UTF-8\"?>
<?xml-stylesheet type=\"text/xsl\" href=\"xslt.xsl\"?>

<states>
";

$states = array( "alabama", "florida", "georgia", "texas" );

foreach( $states as state )

{

$table = mysql_query( "SELECT * FROM $state" );

print "
<$state>
";

$template = "
<realtor>
";

for( $x = 0 ; $x < mysql_num_fields( $table ) ; $x++ )

$template .= "
<" . mysql_field_name( $table, $x ) . ">

[ $x ]

</" . mysql_field_name( $table, $x ) . ">
";

$template .= "
</realtor>
";

while( $data = mysql_fetch_row( $table ) )

{

$currentTemplate = $template;

foreach( $data as $key => $value )

$currentTemplate = str_replace( "[ $key ]", $value, $currentTemplate );

print $currentTemplate;

}

print "
</$state>
";

}

print "
</states>";

?>
[/code:mvhtkkostv]

then create an xsl-stylesheet to style the hole thing...

><font color="black">Simon Käser</font id="black">
>http://endlessx.com

[subhash]

Hi simu,

Thanks for the reply. So, the script example you gave above would print all of them out correct? I am trying to make one definition of the realtor class I suppose:

<Realtor Name>
<Company Name>
<Address>
<phone>
<email>

Would a problem occur if there are not values for one of those fields. For example, not everyone has a company name. Also, in the Realtor name field, there is sometimes a website associated with it, and sometimes not. Would problems occur?

Thanks again for your help!



Subhash

[simu]

no there should not occur any problems if there are empty fields...

just make for each state a db-table, the produced xml file will just take the field-names as tag-names.

><font color="black">Simon Käser</font id="black">
>http://endlessx.com

[weblogic]

--- Sorry for the upcoming RANT --

Yes, let me pull out a recordset from a DB, then transform that recordset into XML, then transform that XML into HTML using XSLT. Yeah that will be a lot quicker then just displaying my recordset straight into HTML because it's XML right. XML is cool and new so it must be the way to go.

--- ---

Ok, enough of that. Sorry. I have to deal with management that has the same logic as this. Unless you are running a DB that can pull the data out as XML you are wasting your time and processor cycles.

[subhash]

<blockquote id="quote"><span class="smalltext" id="quote">quote:<hr id="quote">Originally posted by weblogic

--- Sorry for the upcoming RANT --

Yes, let me pull out a recordset from a DB, then transform that recordset into XML, then transform that XML into HTML using XSLT. Yeah that will be a lot quicker then just displaying my recordset straight into HTML because it's XML right. XML is cool and new so it must be the way to go.

<hr id="quote"></blockquote id="quote"></span id="quote">

lol...

I dont have to use XML, it is not necessary at all...Since I do not know that much about it, I just figured it might be easier to use it. Basically, all I want is a generic form of how a listing will look, such as they are now..And I want the file to pull each record from the database and put each field into its corresponding place in the form until it reaches the end of that state's listing, and I would prefer to use ASP. I think I know how to pull the records from the database, but not how to display them in the format I want..

Thanks again.

Subhash

[Mark]

I think you are better off with a clearly designed HTML table. Some loops to get the records out of the database and you're "done".

- [url="http://neo.dzygn.com/"]Mark[/url:ncw3y3qncz]

[Tim Scarfe]

Weblogic is totally correct.

You only need the XML stage if other factors are involved, i.e. you are running an XML Web service, and other things need to consume the data. There are all sorts of cache considerations too, if you don't want the database to be hit too much but it is irrelevant here.

--
Regards,
Tim Scarfe <tim@developer-x.com>
http://www.developer-x.com

[nhavar]

It seems there's some conflict over why XML is being used. While it's true that some people will jump on the bandwagon and use XML just 'cuz, there are still some legitimate reasons to use it.

XML can most often be used in shared services, when the data that's being requested might go to more than one client/agent. It can be used for portability in case the person receiving the file wants to view it formatted on screen but also be able to import it into other external applications for use. It can be used as a method for maintaining modularity of code, getting one step closer to seperating data, structure, style, and function.

For instance we use Java Servlets to communicate using CORBA to multiple back ends for data retrieval. This data is then put into XML format and sent out to the client which may be the IVR, the Internet portal, the Intranet portal, an external client, or to another back end system that understands XML. If it's being shipped to certain clients it gets transformed using XSLT into either a web page, another xml file, or to PDF depending. Then there is also the ability to cache requests and reuse them without having to hit the backend constantly. You can then take those cached requests and pick out the nodes that you want or transform them into intirely new output depending on your needs. We have a fairly complex system and so using XML helps keep things in a more layered and organized system. It's also helped to reduce the code creep of business logic ending up in JSP/ASP pages that often happens.

I agree that PHP/ASP/JSP are probably more straightforward for small simple database hits and views. XML would be more useful if you knew you were going in a certain direction.