Anyone want to takeover this database?


We are none of us getting any younger and those of us who are approaching the twilight years (I was born in 1935) would like to think someone would be there to carry on the work.
I would offer anyone who is interested all the files and I would give a free hand in the further development of the database. This could be immediately or at some agreed time in the future.
So here is a brief resumé of the structure of my dance database and the way in which it is manipulated.

The tables in MS-Access

Although MS-Access is a relational database, I do not use it as such. The reporting and linking of tables which can be done in MS-Access I achieve with PHP on the website itself.
The most important table is called dances and a few lines from it look like this:

The columns have been collapsed so that it fits on the computer screen and the column headings cannot be read, so here is a list:

  1. ID – an automatically generated index number.
  2. DANCE TITLE – the title of the dance.
  3. ALT Ÿ alternative title.
  4. FOR – formation, a two-digit code.
  5. F1 to F12 – figures, each is a three-digit code.
  6. CD1 to CD8 – recordings, each is a three-digit code.
  7. AUTHOR – author, a four-digit code.
  8. S1 to S8 – sources, each is a four-digit code.
  9. Style – indication whether dance is in New England or other style (not yet implemented).
  10. WEB – link to website, if any, where the dance description can be found, using the four-digit author code.

It is important that this order of the columns is maintained when making the text files for uploading to the website. I sometimes change them round when checking or entering data but always put them back before uploading data.

Here are a few lines of each of the look-up tables:

The PHP read-out scripts

If you don’t understand PHP, you don’t need to read any further. You only need to understand it if something goes wrong, or if you want to change anything about the way the SQL tables are interrogated for the website.
In a nutshell, PHP (Personal Home Page) is a script which runs on the server and prepares HTML documents for display on the user’s browser.
The classic PHP text looks like this:

This returns the text Hello World

When a new dance is entered in the database, it is given a new unique ID. When a dance is deleted, its ID is also deleted and never reused. Therefore the highest ID is larger than the number of entries. The following script determines the number of dances in the database:

Number of dances is 19936

and this script returns the highest ID in the database:

Highest ID is 20490

The database tables can be interrogated in various ways. As an example consider entering a dance title (or part thereof) and then hitting the submit button. The text for this is like this (this is not PHP):

The title is entered in a FORM and passed to the page databaseresult.php for analysis.
The word (or words) typed are passed as ‘keyword’.
First a number of tests are carried out to make sure there has been no attempt to hack the site.
Then ‘keyword’ is tidied up so that it will be useful.
Finally the ‘title’ column in the dance table is searched for anything like the keyword.
If more than one answer is found, a list is presented, if only one answer is found the dance record card is shown and if no answer is found, a message appears to that effect.
I’m not going to reveal the scripts used by this document because a hacker could see something I have missed!

This document is still being written and may be added to later.