Re: Problems combining two criteria in a select statement
Niel Archer <[email protected]>
| Newsgroups | gmane.comp.php.windows |
|---|---|
| Message-ID | <[email protected]> |
> 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
--
PHP Windows Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php