Can anyone help?
I have a menu item that now nicely can show off all wood species in a
table called "species". The reader can then choose an alphabet to filter down
the amount of data being shown (instead of the whole 6,500 possible records).
All that (and in no small part in thanks to the help I got a while
ago from this group)
works great. The select statement for that is:
$alphaquery = "SELECT * FROM species WHERE species_name LIKE
'".$letter."%' order by species_name";
You can view how nicely this is now working at:
http://www.prowebcanada.com/taxa/alphaspecies.php
You can even pick an alphabet and see that it filters properly.
Now I made a copy of this file and want to limit the woods shown only
to commercial
woods for a new menu choice. In a column called "commercial_wood" in table
'species', I am marking all records of commercial woods in that
column as "Commercial".
Just as in the full viewing choice, I want the user to be able to
filter the volume of records
to view by being able to pick an alphabet so there is a manageable
number of records to
show each time. Most readers from the general public will wish to
look up woods that can be
bought instead of the thousands of obscure species that cannot.
I have tried various optional select statements, even putting
brackets around the two
conditions for the where statement as:
$alphaquery = "SELECT * FROM species
WHERE (species.commercial_wood = 'Commercial')
AND (species_name LIKE '".$letter."%')
order by species_name";
This still half works in the sense that it still does query the
'species' table to find all records
tagged as 'commercial'. I don't get an SQL or PHP error showing.
However, as a user when I
then try to pick an alphabet to filter this further, I get a "page
not found" error appearing. If you
care to try it you can:
http://www.prowebcanada.com/taxa/alphacommercialspecies.php
I am so close yet so far from what I am trying to accomplish. I even
tried to reverse the order
of the two criteria with no success. Can any of you help me know what
statement (or code) I should use that will both list all commercial
woods AND have the alphabetical menu for them
still working?
I am guessing that you should not need all the code from that page
but I will include it here
anyway just in case.
Much appreciation for your help,
Bill Mudry
Mississauga, Ontario
================== PAGE CODE FOLLOWS ===========================
Ignore the code lines commented out. Many have been for tracing and
debugging before.
<?php
//////////////////////////////////////////////////////////////////////////////////////
// File: Alphacommercialspecies.php
// Date last revision: Started June 10, 2010
// Description: Designed to show species marked only as commercial woods.
// Includes alphabetical menu.
//
//
//////////////////////////////////////////////////////////////////////////////////////
echo "<HTML>";
echo "<head>";
echo "<title>TAXA: Commercial Wood Listing</title>";
echo "</head>";
echo "<body>";
//$baseLink = "alphacommercialspecies.php?letter=";
// Assuming your file name is "index.php"; set up the base link for all pages
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");
echo "<table border='3'cellpadding='20' bgcolor='ivory'
align='center'><tr><td valign='top'>";
$letter=="";
// Printing alphabet with links
echo "<h2 align='center'>Commercial Woods</h2>";
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";
}
echo "<br></td></tr></table><br />";
// Now pick up a user chosen alphabet
// ---------------------- Start of display table. ---------------------------
echo "<table border='3'cellpadding='20' bgcolor='ivory' align='center'><tr>";
echo "<td valign='top' style='white-space: nowrap'>";
$letter = $_GET["letter"];
//echo "\$letter on line 27 is - $letter.<BR /> ";
if (isset($_GET["letter"]) && $_GET["letter"] != "")
{
Echo "You picked $letter <br />";
}
else {
Echo "Please choose a letter <br />";
};
//$letter = $_GET["letter"];
//Echo "\$letter is still readable as $letter <br />";
///////////////////////////////////////////////////////////////////////////////////////
// Now that the user has chosen a letter, go get the species starting
with that letter
///////////////////////////////////////////////////////////////////////////////////////
$alphaquery = "SELECT * FROM species
WHERE (species.commercial_wood = 'Commercial')
AND (species_name LIKE '".$letter."%')
order by species_name";
// AND species_name LIKE '".$letter."%'
$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();
$l=0;
$m=1;
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.
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////
$camera_icon = "images/cameraicon01.jpg";
$showcamera = "<img src = $camera_icon>...";
if($result5)
{
while($row5 = mysql_fetch_array($result5))
{
extract ($row5);
$l++; // 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);
if ($picture_filename1)
{
echo $showcamera; // the only time it
should show a camera icon
};
echo '<a href="displayspecies.php?&species_name='.$species_name.'
order by species_name">';
echo "$m - $species_name ";
echo"</a>.<br>\n";
$m++; // counter to increment for the next species
}
}
else echo "No species found";
$sciName="";
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>";
$letter="";
$species_name="";
echo "</body>";
echo "</html>";
?>`
