This describes a method for getting information out of the TP database, onto your webpages without having to create individual pages for each image by hand. Have a look here for a working example; currently only the Antarctic, Sub-antarctic, Indian, Chilean and Bolivian areas are set up.

Having said that, there's still a fair amount of work involved, and you'll need to know your way round a spreadsheet pretty well, and possibly a little Javascript too. I can't provide a one-size-fits-all solution, because everyone will organise their images a different way.

This is not intended for anyone who wants a simple, three-click way of making automatically-formatted HTML pages. You'll need to edit HTML, Javascript and a fairly nasty spreadsheet formula by hand.

Before you do anything else, make a backup of your .td4 database. We'll be accessing this directly and, while I've had no trouble, if your database gets scrambled it's not ThumbsPlus' problem - or mine. Continue at your own risk.

Still here? Then this is the plan:

  1. Alter Uwe's image viewing page to access a text file, stored on the webserver. This text file will contain a list of filenames, and (and here's the good bit) any other data you want to display; the modified HTML file will pick out the appropriate entries, format the data and display it using Javascript.
  2. Generate this textfile using (for me at least) OpenOffice Calc. Just as an aside, this would be vastly easier with a command-line SQL prompt - all of the nastiness to come is due to OpenOffice (OO) being 'Helpful'.
    I'm sure Excel will also do the job, but expect things to be slightly different to my descriptions.
So here goes.

The image.html page

Editing yours

My version can be found here. Save it to disk, where it will be in the root folder of your website. Mine contains a lot of extra formatting, but the relevant bits of Javascript are these:
<script language="Javascript">
  var name=window.location.search.substring(1,window.location.search.length);
  var dir=name;
  var comments=[[,]];

  name = name.split('/'); name = name[name.length - 1];
  name = name.split('.'); name = name[0];
  dir = dir.substring(0,dir.lastIndexOf('/'));
  document.write('<script language="Javascript" src="' + dir + '/comments.txt"></'+'script>');
</script>


<script language="Javascript">
for (i=0; i <= comments.length; i++) {
  if (comments[i][0] == name) {
    document.write('<B><I>'+comments[i][1]+'</I></B><BR><FONT SIZE="-1">Original format: '+comments[i][3]+' '+comments[i][2]+'</FONT>');
    break;
  }
}
</script>
This expects that an image name is given as part of the URL, something like this:
http://www.mysite.com/image.html?Some/Pics/image_01.jpg
which would tell the page (originally Uwe's code) to load the image at Some/Pics/image_01.jpg. The first part of the script splits this into bits - the folder that the file is in, and the base name of the image. That is, the name without the file extension. For this example, that would be "image_01".

Pay attention, here comes the science bit. The script uses the folder name to generate a reference to another script on the fly; in fact, it tells the browser to load the text file which we're going to generate later on as if it were a piece of Javascript code. (It is!). What happens is that you have a different comments.txt file in each folder, which contains information for each image stored in the same folder.

The next section of script is about as simple a program as it's possible to get. It just goes through the list, matching filenames with the one that's about to be displayed. If one matches, it formats the information, prints it and quits. If nothing matches, nothing gets displayed.

Using Thumbs+ to generate index pages that point to image.html

Using the Web Page Wizard, you'll need to select Custom URLs/Folders, then Next.
In the "Image URL" text box, use /image.html?./ before the 'normal' folder. For example, if your images were going to be held on the webserver in a folder called 'Gallery1/Images/', the new URL would be /image.html?./Gallery1/Images/ .

The way I work is to create a Gallery, add all the images I want on the web to it, and then use the WPW on that Gallery. Each gallery then gets its own folder on the webserver, rather than having one vast folder with oozens of images. This is A Good Thing™, because you must also switch off the Ensure unique file names and Prefix file names with gallery or found list name - the file names on your web server must match the file names in the database, or there's no way to match them up later.

Note that the file type doesn't need to match - if your original image is called image_01.tif and the one on the web is called image_01.jpg that's OK.

Generating the text files

This is the tricky part. If you've got less than a dozen or so images to show, it'll be easier to do this bit by retyping things - honestly. Skip
down a bit to see what's expected.

Of course, if (everyone shout together) ThumbsPlus could save out text reports to disk this would all be much easier and faster. However :)

Define a DataSource

Run OpenOffice Calc, and select the menu Tools->DataSources. Click on New Data Source, database type ODBC, then the ... box after Data source URL. Next click on Organize, Add, select 'Microsoft Access Driver (*.mdb)' from the list and Finish. You're not finished yet!

Name your data source, then click on Select... in the Database section. A file dialog will appear; change the 'List Files of Type:' to 'All Files' and then select your ThumbsPlus database file. You'll need to locate it on disk - I'll assume you know where it is! (Tip: search on files called '*.td4').

You can inhale again now. Click on the various 'Ok' buttons (four of 'em), and you should be back where you started with an empty spreadsheet. Yippee.

Create a Query

Again go to the Tools->DataSources menu. Select your new data source from the list, then go to 'Queries'. Click on the first available button - Query Design. A new window should appear, with another one containing a list of tables with familiar names. Choose 'Gallery' and 'ThumbnailGallery'. I'm also going to add in 'UserFields' for this example - select that too. Click on 'Close'.

Next we need to tell OO how the tables relate to each other. This is important, and if you get it wrong then who knows what results you're going to get. For the tables we've selected, the joins are these:

ThumbnailGallery.idGallery -> Gallery.idGallery
ThumbnailGallery.idThumb -> UserFields.idThumbUDF
To make the joins, choose the first field in ThumbnailGallery and drag it on top of the corresponding field in the other tables; for example, drag ThumbNailGallery.idGallery and drop it exactly on Gallery.idGallery.

Now we need to tell OO which fields we want to see. Drag them down into the bottom panel in this order:

Gallery.Name
ThumbnailGallery.name
ThumbnailGallery.annotation
UserFields.uf_Film_Format These will be different, depending on your defined user fields in ThumbsPlus
UserFields.uf_Film_Type
You should use the 'Alias' bit on the bottom panel to assign a name to the fields. If two fields have the same name, the Microsoft database engine gets horribly confused and only bothers returning one field. Set the alias for the first field to 'Gal_Name' and the second to 'Thumb_Name'. Now you can click on the 'Run' button and see if everything's working. Go make some tea (or something a little more fortifying), this may take several minutes. I'm serious - I've seen OO take over half an hour on my Athlon 2100+ to return a couple of thousand rows of data. In comparison, other tools (such as blueshell DataGuy) running the exact same query on the exact same data (at the exact same time!) return the results instantly.

Assuming everything worked, click on 'Save As' and name your query. For some reason, this also took an extremely long time for me. Important - I've joined to the UserFields table. If nothing exists in that table for a given image, no data will appear. (Tech note - large databases allow for this by simply adding (+) to the table name in the FROM clause, but it appears the Microsoft SQL engine doesn't. Duh.) Make sure all of the images you're exporting have something set under UserData in ThumbsPlus - or alternatively, don't use UserData on the web!

Import data into a spreadsheet

Select Tools->DataPilot->Start. Now choose "Data source registered in OpenOffice.org". A new dialog appears; choose the correct data source, set the 'Type' box (the third one) to Query and then go back to the second box and pick your query from the list. Click 'Ok', and be prepared for a wait.

Next, drag the headings you have onto the ROW part of the 'DataPilot' display. This will create data in columns - don't ask, I don't know either. Press 'More' and switch off the 'Total Columns' and 'Total Rows' checkboxes. Press 'OK', and wait. Again. If your first two columns look alarmingly similar, you forgot to assign different names to the fields, and OO's cocked up your query. Go directly to define query, do not collect 200.

Eventually Calc will show all the fields you chose, as columns in a spreadsheet. Now, it would be nice if you could just save the resulting spreadsheet as plain text but OO won't allow that. It also screws up a simple copy-and-paste by inserting lots of spaces where they don't exist. This means that we're going to have to join all the columns into one, using OO, before we can get the data out as plain text - hence the nasty formula I promised.

My data is laid out like this:

ABCDE
Gallery NameThumbnail NameAnnotationUser Field : Film SizeUser Field: Film Type

Find the particular gallery you exported to the web by looking at column A, and paste the following formula into the first blank column after the first line of data - for me, that's column G because OO claims one extra column to the right of the actual data.

=CONCATENATE("['";IF(ISERR(FIND(".";B2));B2;LEFT(B2;FIND(".";B2)-1)); "','"; SUBSTITUTE(SUBSTITUTE(C2;"(empty)";"");"'";"&apos;"); "','"; D2; "','"; E2; "'],")

This formula does a little pre-formatting of your chosen data. You will need to change it to suit your particular needs! Let's split it down into smaller chunks:

Now, see all the bits in red? They're cell references in the spreadsheet. The first part (B) is the column (across the top), the second (2) is the row (down the left). You need to change these so that they point at the appropriate data. Say your first line of relevant data appears in row 127. Paste the formula into cell G127 and change all the 2s to 127s. Once you've done this, you should get something like

ABCDEFG
........................
127 Scan001234.tifThis is my annotation35mmPositive  ['Scan001234','This is my annotation','35mm','Positive'],

appearing. Column G is the one that will go into the text file on disk. It's important that the first part of the output in column G is the filename! After that, things can go in any order you like.

What happens is that the Javascript uses the first field to match against, and the others just as text to output. It numbers them from zero, so in the script above comments[i][0] refers to the filename, comments[i][1] to the annotation, comments[i][3] to the film type etc.

Next we need to duplicate this data for each thumbnail you have on the web. Easy - select the cell with this formula (G127 in this example) and it should get a black box around it, with a smaller black box on the bottom right. Click and drag the smaller box downwards, and OO will duplicate the formula in each cell with the correct values already set.

Get the data out of OpenOrifice and onto disk

OpenOffice still won't save the data to disk as plain text. Instead, select all the data you want out (e.g. cells G127 to G150, ignore the original data) and select the Edit->Copy menu. Now run Notepad, and type:

var comments=[

Next, paste in all that luvverly data by selecting the Edit->Paste menu. Usually Ctrl-V does the same thing, but I find that when pasting from OO to Notepad in Windows XP, it doesn't. Go figure.

Delete the very last comma on the last line of data and add

]

to finish off. You should now have something like the following. Note that if there's any space outside the quote marks - that is, between the text and the commas, or the the text and the brackets, Opera won't display anything.

var comments=[
['Scan000179','Glacier snout at Signy','35mm',''],
['Scan000187','Resting elephant seals','35mm',''],
['Scan000205','Rich T, Dave and Liz at Signy','35mm','']
]



<--- No comma here!

Yee-ha! Save this as comments.txt in the same directory as your images, and let's summarize.

You should now have:

and You'll still need to copy and paste the text output a bit, but it should be much easier to perform updates than to get everything set up in the first place. Open the spreadsheet, right-click somewhere on the data area and select 'Refresh'.

Testing the whole kit an' caboodle

Um, load your favourite browser, bring up the index page and try a link.

... and finally

Get creative with the javascript! You've got access to any data you want, so think of new ways to deal with it.