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 photo scans of as many woods 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 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.
