Introduction

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 Database

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

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

Feature

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

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

Exception

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 Query

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

Other stuff

I was going to add some more stuff here, but haven't quite got round to it yet. I will do one day....maybe....or then again maybe not. Actually, probably not. So this will have to do.

If you have any comments or suggestions then feel free to send me an email by following the link below....