Problems combining two criteria in a select statement

Re: Problems combining two criteria in a select statement

> 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.
>
> > //////////////////////////////////////////////////////////// //////////////////////////
> // File: Alphacommercialspecies.php
> // Date last revision: Started June 10, 2010
> // Description: Designed to show species marked only as commercial woods.
> // Includes alphabetical menu.
> //
> //
> //////////////////////////////////////////////////////////// //////////////////////////
>
> echo "";
> echo "";
> echo "";
>
> echo "";
>
> echo "";
> //$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 "

"; > $letter = $_GET["letter"]; > > //echo "\$letter on line 27 is - $letter. "; > > if (isset($_GET["letter"]) && $_GET["letter"] != "") > { > Echo "You picked $letter "; > } > else { > Echo "Please choose a letter "; > }; > //$letter = $_GET["letter"]; > //Echo "\$letter is still readable as $letter "; > > //////////////////////////////////////////////////////////// /////////////////////////// > // 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 \n"; > Echo MYSQL_ERROR(); > > > $l=0; > $m=1; > > echo " Each link leads to more information on the > chosen botanical species "; > > echo ""; > echo ""; > echo " "; > > //////////////////////////////////////////////////////////// /////////////////////////////////////////////////////// > // 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 = "..."; > > 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 " "; > $l=0; > > }; > $picture_filename1 = trim($picture_filename1); > > if ($picture_filename1) > { > echo $showcamera; // the only time it > should show a camera icon > }; > > echo ''; > echo "$m - $species_name "; > echo". \n"; > $m++; // counter to increment for the next species > > > } > > } > else echo "No species found"; > $sciName=""; > > > > > echo " "; > > // ---------------------- End of display table. --------------------------- > > Echo " "; > > > echo " End of Listing for All Woody Species > starting with $letter "; > $letter=""; > $species_name=""; > > echo ""; > echo ""; > > > ?>` > -- Niel Archer -- PHP Windows Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Re: Problems combining two criteria in a select statement am 10.06.2010 19:03:57 von Bill Mudry --=====================_120337767==.ALT Content-Type: text/plain; charset="us-ascii"; format=flowed At 12:13 PM 10/06/2010, you wrote: > > > 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 --=====================_120337767==.ALT--