After a few months working on another website, I am back finishing a list of
coding
tasks on my TAXA Wood Knowledge Base. One of the most important pages in the
site is the one that lists (so far) over 15,000 different botanical names of
woods.
Each single name listing is also linked to the corresponding data page for that
wood where details are given on each wood. On such data pages are wood photo
scans of as many as I can supply (So far about 2,100 out of the 15,000 listed).
Rather than cluttering each page with more information than many readers may
want, I have three pop-up pages on each data page to show more colour photos,
UV fluorescence photos and photomicro photos under the microscope.
With only that, readers looking at the large list of species would find all
these scans and photos by coming to them randomly with many species having
none. To make this far easier to actually pick which woods have scans, uv
photos and so on I am adding "look ahead" icons. If a listed wood has any of
these categories have any of these scans on their data pages or on the pop-up
pages, there is or will be an icon beside the botanical name to make it easy to
realize there is and then visit those.
If any of this seems too complex to understand right away, the site is at
www.woodsoftheworld.org under the menu item Woody Species. To drop right out of
the frames environment directly, the page is directly at
www.woodsoftheworld.org/alphaspecies.php.
The color photos are showing fine for a few months already as are the camera
icons
beside the species names for some months now. I have about 100 UV fluorescence
photos that are also showing off ok but I am now working on placing a small
circle with a "UV" in it for all UV photos on woods that do shine under UV.
That is where I have come up with more difficulties than I ever expected.
Now at least I hope this is enough to illustrate what I am trying to do. One
reason it was easy to get the camera icon working well is because the field
where addresses to pick up the camera icon as needed is in the same 'species'
file as is needed to store all basic information on each species. After that
file grew to at least 18 fields, I thought that maybe I should finally pay some
attention to some normalization and store the UV photo addresses in its own
table of 'uvphotos'. It has only 4 fields (uvID, speciesID, species_name and
uv_filename.
The 15,000 names are extracted from the species table and displayed with a
while loop. Each time through that loop it posts the next species name, so I
found it necessary to run a SELECT query within that loop to find if there is
any UV photo address per listed species and to echo out the UV icon if there
is. Because these photo addresses are stored in a separate table, I had to use
a JOIN in the SELECT STATEMENT. To test for the right statement since you
cannot use $variables in MySQL like you can in PHP, I chose a species name that
I knew did have a uv photo.
I found that the query that would find the one unique address to use is:
$uvquery = 'SELECT u.uv_filename
FROM `uvphotos` u
JOIN `species` s ON u.species_name=s.species_name
WHERE u.species_name="$species_name"';
First, then, this ran without any error reported and listed all the species that
do have uv photos for which the uv icon should show. By substituting the
$species_name with one that really does have a uv photo such as Acacia
aneura,the query does properly bring forth the address where the photo is
stored.
In the page (alphaspecies.php), the $uvquery runs fine without errors reporting
and so does $uvresult = mysql_query($uvquery). Running .....
$uvrow=mysql_fetch_array($uvresult, MYSQL_ASSOC); should work ok, too
but
when I try to use extract($uvrow) it constantly complains that it cannot find a
valid array to expand. Doing a row count shows that there are no rows in the
query results even though they list beautifully running the query in phpmyadmin.
The code I have now that I will paste below runs ok without reported errors but
a debug statement reports "No uv photo" both for the ones that do not have one
AND for those that do! <argh!>. I cannot find why it does not pick up the
existence of a uv photo where they do exist. It should be posting a uv icon
beside each species that does have one.
Any code that is related on the issue starts at about line 180 or so. Here,
then is the code for the whole page. You will find quite a few commented out
lines that were used just to understand and try to debug what was going on.
================================================================================
<code:php>
<?php
session_start();
//$_SESSION['$s'] = "speciesname";
error_reporting(-1); // Report every single error going.
ini_set('display_errors', 1); // Show them in-line.
set_time_limit(0); // No time limit.
?>
<HEAD>
<TITLE>TAXA: Display of species by alphabetical groups</TITLE>
<STYLE type="text/css">
A:link { text-decoration: none }
A:active { text-decoration: none }
A:visited { text-decoration: none }
</STYLE>
</HEAD>
<BODY>
<?php
//////////////////////////////////////////////////////////////////////////////////////
// File: alphaspecies.php
// Date last revision: June 17, 2010
// Description: This file offers the user to pick an alphabet that limits the
species
// shown to those having botanical namss starting with that alphabet
and
// displays all the data and picture(s) on one chosen species
// It also will hold the icons that look ahead to let viewers know if
each species
// has a scan picture, additional pictures, a UV fluoresence picture
and/or a
// photomicro photo
//
//
//
//
//
//
//
//////////////////////////////////////////////////////////////////////////////////////
//$speciesname = "";
//$letter = "";
$uv_filename = "";
//rowcount = "";
// Assuming your file name is "index.php"; set up the base link for all pages
$baseLink = "alphaspecies.php?letter=";
////////////////////////////////////////////////////////////////////////////////////////
// CONNECT TO THE DATABASE
////////////////////////////////////////////////////////////////////////////////////////
include ("connecttotaxa.php");
$connection = mysql_connect($hostname, $username, $password)
or die("Unable to connect to database server");
$db = mysql_select_db($dbname, $connection)
or die("Unable to connect to database");
////////////////////////////////////////////////////////////////////////////////////////
// HOPEFULLY CONNECTED TO THE DATABASE
////////////////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////////////////
//
// This section sets up a choice of alphabet for users to pick to cut down on
the volume
// of species data.
//
////////////////////////////////////////////////////////////////////////////////////////
// SET UP A LETTER DISPLAY AND PICKING AREA.
echo "<table border='3'cellpadding='20' bgcolor='ivory' align='center'><tr><td
valign='top'>";
//$letter=="";
// Printing alphabet with links
echo "<h3 align='center'>Pick Species by Alphabet</h3>";
for($a=65;$a<(65+26);$a++)
{
print "<a href=\"".$baseLink.chr($a)."\">".chr($a)."</a>\n";
}
$letter = "";
@$letter = $_GET["letter"];
//echo "\$letter on line 27 is - $letter.<BR /> ";
echo "<br />";
echo '<div ID="letterbox" align="center">';
if (isset($_GET["letter"]) && $_GET["letter"] != "")
{
Echo "<font color='888'>You picked letter $letter <br /></font>";
}
else {
Echo "Please choose a letter <br />";
};
echo "</div>";
//$letter = $_GET["letter"];
//Echo "\$letter is still readable as $letter <br />";
// Close off the display alphabet box
echo "<br></td></tr></table><br />";
////////////////////////////////////////////////////////////////////////////////////////
// END OF ALPHABET CHOOSING AREA
////////////////////////////////////////////////////////////////////////////////////////
// Now pick up a user chosen alphabet
////////////////////////////////////////////////////////////////////////////////////////
// --------------------------- START OF MAIN DISPLAY TABLE
----------------------- //
////////////////////////////////////////////////////////////////////////////////////////
echo "<table border='3'cellpadding='20' bgcolor='ivory' align='center'><tr>";
echo "<td valign='top' style='white-space: nowrap'>";
///////////////////////////////////////////////////////////////////////////////////////
// Now that the user has chosen a letter, go get the species starting with that
letter
///////////////////////////////////////////////////////////////////////////////////////
$alphaquery = "SELECT * FROM species WHERE species_name LIKE '".$letter."%'
order by species_name"; //
//Echo "Line 124 \$species_name is - $species_name<br>\n";
$result5 = mysql_query($alphaquery)
or die(mysql_error());
//var_dump($result5);
$row=mysql_fetch_array($result5, MYSQL_ASSOC);
//Echo "\$row is - $row<br>\n";
Echo MYSQL_ERROR();
///////////////////////////////////////////////////////////////////////////////////////
// Set up the query to look ahead whether or not the present species has a UV
picture
///////////////////////////////////////////////////////////////////////////////////////
/*
$numrows = mysql_num_rows($uvresult) or die (mysql_error());
//ECHO "Number of rows is - $numrows<br />";
//$row = mysql_fetch_array($uvresult);
//ECHO "\$uvrow on line 104 is - $uvrow<BR />";
//ECHO "The path and file name, line 109 is $uvrow('uv_filename')<BR />";
*/
$l=0;
$m=1;
$n=0;
echo "<H4 align='center'>Each link leads to more information on the chosen
botanical species</h4>";
echo "<table border='3'cellpadding='20' bgcolor='ivory' align='center'><tr>";
echo "<td valign='top' style='white-space: nowrap'>";
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// Now as each species is generated in a list of the woody species of the
chosen genus, we want to show a small camera
// icon (as images/cameraicon01.jpg) in front of each listed species ONLY if
the species has a scan or photo
// stored in the 'species' table under column 'picture_filename1'. The program
has to look ahead in the 'species'
// table for all of them to see which ones should and which should not have a
camera icon beside them. The whole
// idea is to let readers be informed on which choices include a scan or photo
before choosing. If there is no
// null or empty value in that location, it is assumed that there is a scan to
view.
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// Set up image name and path for showing a camera icon for species that have a
scan picture
$camera_icon = "images/cameraicon01.jpg";
$showcamera = "<img src = $camera_icon>";
// Set up image name and path for showing a UV icon for species that have a UV
fluorescence photo
$uv_icon = "images/uv.gif";
$showuv = "<img src = $uv_icon>";
/////////////////////////////////////////////////////////////////////////////////////////////////////////
//
// LOOPING THROUGH SPECIES STARTS HERE
//
/////////////////////////////////////////////////////////////////////////////////////////////////////////
//var_dump($result5);
if($result5)
{
while($row5 = mysql_fetch_array($result5))
{
extract($row5);
//Echo "Line 206 \$species_name is - $species_name<br>\n";
//$species_name = 'Acacia aneura'; //Debug statement to set species_name
temporarily
/////////////////////////////////////////////////////////////////////////////////////////////////////////
// UV Lookahead section starts
/////////////////////////////////////////////////////////////////////////////////////////////////////////
$uvquery = 'SELECT u.uv_filename
FROM `uvphotos` u
JOIN `species` s ON u.species_name=s.species_name
WHERE u.species_name="$species_name"';
//Echo "\$speciesname at line 215 is $species_name"; Debug statement
$uvresult = mysql_query($uvquery)
or die(mysql_error());
//var_dump($uvresult);
//Echo "<br />";
$uvrow=mysql_fetch_array($uvresult, MYSQL_ASSOC);
//extract($uvrow);
//Echo "Line 226 - \$uv_filename is - $uv_filename<br />";
$rowcount = mysql_num_rows($uvresult);
//var_dump($rowcount);
//Echo "<br />";
//Echo "Number of UV rows, line 235 is $rowcount<br />";
//if(mysql_num_rows($uvresult) > 0){
//$uvrow = mysql_fetch_array($uvresult, MYSQL_ASSOC);
//ECHO "\$uvrow on line 237 is - $uvrow<BR />";
};
//$uv_filename = $uvrow['uv_filename'];
//ECHO "\$uvrow on line 240 is - $uvrow<BR />";
/////////////////////////////////////////////////////////////////////////////////////////////////////////
// UV Lookahead section ends
/////////////////////////////////////////////////////////////////////////////////////////////////////////
$l++; // Increment counter to set maximum columns lengths
//echo "'\$m is - '.$m";
if ($l>100)
{
echo "</td>";
echo "<td valign='top' style='white-space: nowrap'>";
$l=0;
};
$picture_filename1 = trim($picture_filename1);
$uv_filename = trim($uv_filename);
echo '<a href="displayspecies.php?&species_name='.$species_name.' ">';
echo "<font color=grey>$m</font> - $species_name";
echo"</a>.";
if ($picture_filename1)
echo "$showcamera";
// the only time it should show a camera icon
//echo "<br />\$uv_filename on line 261 is - $uv_filename";
//echo "Line 263 - The UV filename is $uv_filename<br />";
if ($uv_filename)
{
echo $showuv; // the only time it should show a UV icon
};
echo "<br>\n";
//ECHO "$showuv"; // hard coded test to place UV icon on all
/*
*/
$m++; // counter to increment for the next species
$n++; // counter to increment to the next uv photo
}
else echo "No species found";
//}
////////////////////////////////////////////////////////////////////////////////////////////////////
//
// END OF DISPLAY LOOP
//
////////////////////////////////////////////////////////////////////////////////////////////////////
//Echo "species_name is - $species_name<br>\n";
echo "<br></td></tr></table><br>";
///////////////////////////////////////////////////////////////////////////
// ---------------------- End of display table. ---------------------------
///////////////////////////////////////////////////////////////////////////
Echo "<br><hr width='400'>";
echo "<H3 align = 'center'>End of Listing for All Woody Species starting with
$letter</H3>";
/////////////////////////////////////////////////////////////////////////////////////////////
// ----------------------------- Total End of Species Listing
-------------------------------
/////////////////////////////////////////////////////////////////////////////////////////////
$letter="";
//$species_name="";
$uv_filename="";
?>`
<!-- -------------------- The following script is needed by GoDaddy for
tracking stats on traffic ---------------->
<SCRIPT type="text/JavaScript">
var TFN='';var TFA='';var TFI='0';var TFL='0';var
tf_RetServer="rt.trafficfacts.com";var
tf_SiteId="44ge27e3f31f6297ededc5ee9d3355117dbc06520fah26";var
tf_ScrServer=document.location.protocol+"//rt.trafficfacts.com/tf.php?k=44ge27e3f31f6297ededc5ee9d3355117dbc06520fah26;c=s;v=5";document.write(unescape('%3Cscript
type="text/JavaScript" src="'+tf_ScrServer+'">%3C/script>'));
</SCRIPT>
<NOSCRIPT><IMG
src="http://rt.trafficfacts.com/ns.php?k=44ge27e3f31f6297ededc5ee9d3355117dbc06520fah26";
height="1" width="1" alt=""/></NOSCRIPT>
</BODY>
</HTML>
</code>
================================================================================
running the query:
SELECT u.uv_filename
FROM uvphotos u
JOIN species s ON u.species_name = s.species_name
WHERE u.species_name = "Acacia aneura"
produces the proper answer only the uv_filename field of
uva/acacia_aneura-mulgaUV02.jpg
At this point, I am stumped to understand why the code I have written does not
successfully bring forth all the photo addresses where they do exist amidst the
15,000 species reported. You can view this right at the output page.
Once this is fixed, it will be much easier to complete the look aheads for the
remaining photomicro and more photos areas --- and a lot closer to finally
finishing all the coding for what I planned to be version 1 of the website.
As always, I am indebted and thankful for your help.
Bill Mudry
Mississauga, Canada.
