This website has been written and developed by in 2004, using a combination of PHP / HTML / MYSQL, plus a small piece of Javascript. It was primarily done, as a learning exercise for me in these languages.
As stated on the ©Copyright Information page, the idea behind this website and data used in this website was obtained from the GUESS program written and developed at North Carolina State University.
The first step in the designing this website was converting the data from the GUESS program into a format that could be used in a MySQL database. The databases provided with the GUESS program, were in 'CDB' format - Compressed DataBase format. A provided program called MASH was used to uncompress the databases, and then a Korn Shell Script on a Unix machine was used to convert the database into insert statements suitable for MySQL. This script tried to identify which parts of each wood name was the Latin Name, the common name and any comments.
The tables created are certainly not the most space efficient solution, but as these databases are not massive I concentrated on speed rather than space.
The tables used are as follows:
| Wood_DB_ID: | Unique ID |
| Wood_DB_Nam: | Name of Wood database |
| Active: | Binary field indicating if DB is active & thus displayed |
| Acknowledge: | Acknowledgement message displayed with DB name |
| Wood_DB_ID: | Database ID (as per wood_db table) |
| Display_Num: | Order that features are displayed in (not necessarily the same order as the feature numbers) |
| Feature_Num: | Unique feature number for each database ID |
| Feature: | Feature description that gets displayed |
| Absent: | Show absent options for this feature |
| Wood_DB_ID: | Database ID (as per wood_db table) |
| Wood_ID: | Unique wood ID |
| Wood_name: | Latin name of wood |
| Common_name: | Common name of wood |
| Comment: | Comment associated with wood |
| F1: | Binary number indicating if feature 1 is present |
| F2: | Binary number indicating if feature 2 is present |
| . | . |
| . | . |
| Fn: | Binary number indicating if feature n is present |
| Wood_DB_ID: | Database ID (as per Wood_DB table) |
| Wood_id: | Unique wood ID (matches wood_id in wood table) |
| E1: | Number to indicate if feature 1 has an exception (0 = no exception, 1 = variable, 2 = questionable) |
| E2: | Number to indicate if feature 2 has an exception. |
| . | . |
| . | . |
| En: | Number to indicate if feature n has an exception. |
The major part of the website design was working out the query to perform to return the correct results, in the same manor that the original GUESS program achieved.
The end query is:
select w.wood_id,
wood_name,
common_name,
(num_required_present + num_present) -
(p1+p2+..+pn + r1+r2+..+rn+0) + (a1+a2+..+an + e1+e2+..+en+0) -
((least(1,ex1))+(least(1,ex2))+..+least(1,exn))+(least(1,ax1))+(least(1,ax2))+..+(least(1,axn))) MISMATCHES,
r1, r2,..,rn,e1,e2,..,en,p1,p2,..,pn,a1,a2,..,an,null
from wood w
exceptions e
where w.wood_db_id='wood_db_id'
and w.wood_id=e.wood_id
and w.wood_db_id=e.wood_db_id
and num_required_present - (r1+r2+..+rn+0)+(e1+e2+..+en+0) -
((least(1,ex1))+(least(1,ex2))+..+(least(1,exn))+0) = 0
and num_required_present - (p1+p2+..+pn+0)+(a1+a2+..+an+0) -
((least(1,ax1))+(least(1,ax2))+..+(least(1,axn))+0) <= maxmismatches
order by MISMATCHES, WOOD
KEY
| p1,p2,..,pn | : | Features selected as being present |
| r1,r2,..,rn | : | Features selected as being required present |
| a1, a2,..,an | : | Features selected as being absent |
| e1,e2,..,en | : | Feature selected as being require absent |
If you have any comments or suggestions then feel free to send me an email by following the link below....