Hi all,

I have a SQL server db containing all of my data. I have written about 10 queries that all return 18 rows , 2 columns of data. I'm trying to setup a web page that display these tables and dynamically update. I have pondered using sql stored procedures and XML but was wondering if anyone had any better idea's of exactly how to achieve this?

Thanks for any info
Retro

depends on what server side code you are running.

How do you mean? The queries I'm running are very simple select statements.

SQL can't write HTML or XML, so you will need something to take the output from the SQL server and translate that into code that can be displayed via a web browser.

SQL can do XML with SELECT [CLAUSES HERE] FOR XML. If all you're trying to do is run the queries and dump the results to the web, any of the dozen easy solutions will do just fine. If you're trying to make the data updatable, where the user can type in new values and save them to the database, you have more work ahead of you.

SQL can't write HTML

The following works easily on MySQL and just builds a HTML table with the contents of col_A and col_B. Ignore the ord column as it's used for ordering and just print out the html column.

select 0 as ord, "<table>" as html
union
select 1, concat( "<tr><td>", col_A , "</td><td>", col_B,"</td></tr>" )
from my_table
union
select 2, "</table>"
order by ord

If you only have 18 rows is it really worth having a database? would it be easier just printing out your 18 values?

Mike

The FOR XML is great if you don't have a high server load pulling the data down. If the data need only be updated say every 15 mins then this is a great way to generate XML data on the backend and use XSL to display it.

Originally Posted by mike_bike_kite

The following works easily on MySQL and just builds a HTML table with the contents of col_A and col_B. Ignore the ord column as it's used for ordering and just print out the html column.

select 0 as ord, "<table>" as html
union
select 1, concat( "<tr><td>", col_A , "</td><td>", col_B,"</td></tr>" )
from my_table
union
select 2, "</table>"
order by ord

If you only have 18 rows is it really worth having a database? would it be easier just printing out your 18 values?

Mike

And you would call the SQL query from a browser and output the data to the browser by using ? ......

Simple web server code, and more intra-server traffic. It's a cool idea, but winds up moving the complexity from PHP to MySQL.

And you would call the SQL query from a browser and output the data to the browser by using ? ......

If his 18 rows are in the database then he'll need SQL to extract them. If he wants to view the output on a web page then he'll need a program running on the server to run the SQL from (if it's MySQL then php is an obvious choice). I was simply trying to save him the time and effort of learning XML and possible XSLT - it just seemed like overkill to me.

If there are so few rows to extract and, if they don't change very often (I'm assuming a lot here), then it might be better not using a database at all and just updating a static web page.

Mike