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 database is maintained as a number of tables in MS-Access in my own computer.
- From time to time the tables are uploaded to an SQL database on my website as text files with comma-separated variables (CSV).
- The SQL database is interrogated by a number of PHP files which are copiously annotated.
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:
- ID an automatically generated index number.
- DANCE TITLE the title of the dance.
- ALT alternative title.
- FOR formation, a two-digit code.
- F1 to F12 figures, each is a three-digit code.
- CD1 to CD8 recordings, each is a three-digit code.
- AUTHOR author, a four-digit code.
- S1 to S8 sources, each is a four-digit code.
- Style indication whether dance is in New England or other style (not yet implemented).
- 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.