Problems combining two criteria in a select statement
in [Windows]
|
From: Bill Mudry on 10 Jun 2010 11:32 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>"; ?>`
From: Niel Archer on 10 Jun 2010 12:13 > 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 Tried this page. Your link address is not being generated correctly. Hover your mouse over 'A' for example and the link is: http://www.prowebcanada.com/taxa/A not http://www.prowebcanada.com/taxa/alphacommercialspecies.php?letter=A as one would expect. Probably because you have the line setting $baseLink commented out. > 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>"; > > > ?>` > -- Niel Archer
From: Bill Mudry on 10 Jun 2010 13:03 At 12:13 PM 10/06/2010, you wrote: ><cut for brevity> > > not found" error appearing. If you > > care to try it you can: > > http://www.prowebcanada.com/taxa/alphacommercialspecies.php > >Tried this page. Your link address is not being generated correctly. >Hover your mouse over 'A' for example and the link is: > >http://www.prowebcanada.com/taxa/A > >not > >http://www.prowebcanada.com/taxa/alphacommercialspecies.php?letter=A > >as one would expect. Probably because you have the line setting >$baseLink commented out. That was it Neil :-) . Much thanks for your astute observation. It can't get better on debugging than to find that just uncommenting a line would fix a problem, right? With your help, I now have a new and important function added to the site :-) :-). ( Next task for me --- a couple cartwheels and smileys ;-) ) Much thanks. Bill Mudry
|
Pages: 1 Prev: PHP Installation Problem Next: Still having a hard time transferring records table to table |