Fog Creek Software
Discussion Board

Knowledge Base
Terry's Tips
Darren's Tips

Importing HTML into variables

A database has a pile of frequently-changing information - phone numbers, or prices, or whatever. I can use any number of techniques to turn the data into HTML (a table structure, for instance). But does anyone have code for importing that HTML structure into a variable - and doing it over and over again?

CityDesk can work with more sophisticated apps for certain situations if you can import data to a variable. Even better would be to allow republication to be scheduled. Then you can have this:

* The app server grabs data at 2am
* The app server dumps the data into a fragment of HTML in a text file at 2.30 am
* The HTML fragment is imported into CityDesk at 3am
* CityDesk publishes the site at 3.30am

It would also be handy to have someone sent an email if CityDesk falls over without completing publication.

Or am I the only one who could use this?

David Walker
Wednesday, June 26, 2002

If the database is accessible from your web server, you could write some server-side scripts to collect it and format it into an HTML fragment. Then you could include it in your CityDesk articles using server-side includes (SSI).

Of course, you won't see what it looks like until you actually publish to the web server, since the fragments to include would live there. On the plus side, you wouldn't need to publish the article for the data to be updated.

A similar approach would be to use php or something in your articles and/or templates to fetch data from a DB on your web server.

Has anyone done anything like this?

Darren Collins
Wednesday, June 26, 2002

The interesting question seems to me to be how to do this without having your server connected to your database. After all, if you have server-database connectivity, you probably don't need CityDesk. I'm interested in the case where you have something like JSP or ASP or ColdFusion running locally to script the database. (These can form the basis of a CMS, of course, but CityDesk has some interesting advantages over these solutions.)

SSI has the weakness Darren noted, and requires .shtml extensions everywhere.

David Walker
Wednesday, June 26, 2002

You can instruct your server to parse all files for SSI's, not just .shtml files.

Thursday, June 27, 2002

Unfortunately enabling SSI for all .html files (via "XBitHack Full") will slow down the server. Plus, you then have the extra trouble of getting your database-generated files up to the server - and as Darren has noted, you lose the ability to preview locally.

David Walker
Thursday, June 27, 2002

What I'd really like to see is a way of referencing external data from CityDesk, similar to what MS Word has. I think this would probably meet the needs David outlines above (if I understand them correctly).

I'd like to have a table in an article that contains up-to-date product prices. In Word, you could create a document that holds a table that is updated from an Excel spreadsheet each time you open it. If CityDesk could do something similar, and update the table from an Excel spreadsheet at publish time, it would be fantastic.

Once you can do that, you can have all sorts of Excel automation going on to retrieve data from SQL databases, process it, format it, flip it sunny side up and serve it into a table in CityDesk. That would be sweet.

Darren Collins
Thursday, June 27, 2002

OK, now that I've thought about the original problem a little more, I can't see why you couldn't do something like this right now.

Excel can open Access databases, which is all CityDesk files really are. So you could write an Excel VBA app that changes a CityDesk variable each time it runs (a CityDesk variable is really just a row in a table).

Next time you publish your CityDesk site, the new variable contents will be inserted into any article that references it.

So, all this is possible but clumsy. It'd be a lot nicer if CityDesk could pull the data in at publish time, rather than relying on other applications pushing the data in when they are run.

If I get some spare time this weekend, I'll have a go at writing a script to do this.

Darren Collins
Thursday, June 27, 2002

*  Recent Topics

*  Fog Creek Home