CINXE.COM

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html>   <head> <META name="description" content="Site information for wood identification database. Written in PHP, HTML and MySQL installed on a Solaris Unix machine running Apache 2"> <META name="keywords" content="MySQL, Apache, PHP, Zend"> <meta http-equiv="X-UA-Compatible" content="IE=EmulateIE7"> <link rel="icon" href="favicon.ico" type="image/x-icon"> <link rel="shortcut icon" href="favicon.ico" type="image/x-icon">  <style type="text/css">  </style> <title>Website Information</title>  <script async src="https://www.googletagmanager.com/gtag/js?id=G-WNV5WBVMMR"></script> <script> window.dataLayer = window.dataLayer || []; function gtag(){dataLayer.push(arguments);} gtag('js', new Date()); gtag('config', 'G-WNV5WBVMMR'); </script> </head>  <body background="Background/image001.jpg" link="#800000" vlink="#800000" alink="#800000"> <form action="index.php"> <Input type="submit" name="submit" value="Back to DB Select Screen"> </form>  <hr> <h2>Introduction</h2>  <p> This website has been written and developed by <SCRIPT>  </SCRIPT> <NOSCRIPT> <I>[Please Enable JavaScript]</I> </NOSCRIPT> 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. </p> <p> As stated on the <a href="copyright.php"><b>©Copyright Information</b></a> 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. </p> <p> 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. </p>  <h2>The Database</h2>  <p> 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. </p> <p> The tables used are as follows: </p> <h3>Wood_db</h3> <table border=1> <tr><td>Wood_DB_ID:<td>Unique ID <tr><td>Wood_DB_Nam:<td>Name of Wood database <tr><td>Active:<td>Binary field indicating if DB is active & thus displayed <tr><td>Acknowledge:<td>Acknowledgement message displayed with DB name </table> <br> <h3>Feature</h3> <table border=1> <tr><td>Wood_DB_ID:<td>Database ID (as per wood_db table) <tr><td>Display_Num:<td>Order that features are displayed in (not necessarily the same order as the feature numbers) <tr><td>Feature_Num:<td>Unique feature number for each database ID <tr><td>Feature:<td>Feature description that gets displayed <tr><td>Absent:<td>Show absent options for this feature </table> <br> <h3>Wood</h3> <table border=1> <tr><td>Wood_DB_ID:<td>Database ID (as per wood_db table) <tr><td>Wood_ID:<td>Unique wood ID <tr><td>Wood_name:<td>Latin name of wood <tr><td>Common_name:<td>Common name of wood <tr><td>Comment:<td>Comment associated with wood <tr><td>F1:<td>Binary number indicating if feature 1 is present <tr><td>F2:<td>Binary number indicating if feature 2 is present <tr><td>.<td>. <tr><td>.<td>. <tr><td>Fn:<td>Binary number indicating if feature n is present </table> <br> <h3>Exception</h3> <table border=1> <tr><td>Wood_DB_ID:<td>Database ID (as per Wood_DB table) <tr><td>Wood_id:<td>Unique wood ID (matches wood_id in wood table) <tr><td>E1:<td>Number to indicate if feature 1 has an exception (0 = no exception, 1 = variable, 2 = questionable) <tr><td>E2:<td>Number to indicate if feature 2 has an exception. <tr><td>.<td>. <tr><td>.<td>. <tr><td>En:<td>Number to indicate if feature n has an exception. </table> <br>  <h2>The Query</h2>  <p>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.</p> <p>The end query is:</p> <pre> 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 </pre> <b>KEY</b> <table> <tr><td>p1,p2,..,pn<td>:<td>Features selected as being present <tr><td>r1,r2,..,rn<td>:<td>Features selected as being required present <tr><td>a1, a2,..,an<td>:<td>Features selected as being absent <tr><td>e1,e2,..,en<td>:<td>Feature selected as being require absent </table>  <h2>Other stuff</h2> 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. <p> If you have any comments or suggestions then feel free to send me an email by following the link below....</p> <SCRIPT>  </SCRIPT> <NOSCRIPT> <I>[Please Enable JavaScript]</I> </NOSCRIPT> </p>  <hr> <form action="index.php"> <Input type="submit" name="submit" value="Back to DB Select Screen"> </form> </body> </html>