php-windows Digest 13 Jun 2010 05:44:27 -0000 Issue 3825
Topics (messages 30150 through 30152):
Re: Problems combining two criteria in a select statement
30150 by: Niel Archer
30151 by: Bill Mudry
Still having a hard time transferring records table to table
30152 by: Bill Mudry
Administrivia:
To subscribe to the digest, e-mail:
php-windows-digest-subscr...@lists.php.net
To unsubscribe from the digest, e-mail:
php-windows-digest-unsubscr...@lists.php.net
To post to the list, e-mail:
php-wind...@lists.php.net
----------------------------------------------------------------------
--- Begin Message ---
> 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
--- End Message ---
--- Begin Message ---
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
--- End Message ---
--- Begin Message ---
Back about May 10, I presented a difficulty I was having trying to
add in more (distinct) records from one
MySQL table into another that permanently holds the most important
data that I have. Despite help from
a couple of you in the forum and numerous efforts by me since,
success on my efforts have been denied
<big sigh!>.
First, some review and introduction:
This is for a knowledge base on all woods around the world. The more
woods can be reported,
the more successful is the project. I have named the project 'TAXA"
(..short for taxonomy). Many of the
other features I have wanted to add have been coming on quite well
but this task is still evading any success.
If you care to directly view what this is about, take a look at
http://www.prowebcanada.com/taxa/.
In fact, you can escape the frames menu and go right to where species
only are displayed:
http://www.prowebcanada.com/taxa/alphaspecies.php.
There are 4 major entry points to all the data but you have to be
concerned with the 'species' level.
The data there is stored in a table named 'species' (ie. -
species.sql). All tables in the project are in database 'taxa'. The
more (botanical) names of woods that I can find and add in, the more
successful is the whole project. So far I am reporting just over
6,200 different woods (as I say, all in species.sql).
The Thorny Problem
Presently I use all kinds of sources to find new woods. The largest
list of woods I have is in a sql file
called 'tervuren', with 11,337 wood names. That is WAAYY larger than
what is in species.sql so there
is a huge interest in copying over these records to species.sql It
would be a huge boost that even with
duplicates avoided, that would almost double the number of woods I
could report!
That is easy said but I have almost being tearing my hair out (...
and I am almost bald anyway ;-) )
trying to accomplish this. To prepare for this:
- I protected the integrity of my original files
(species.sql and tervuren.sql) by copying them
as working files species_master.sql and
tervuren_target.sql. It is with these that I will
(somehow) copy all wood species names that are NOT in the
species_master table that
are, however, in the tervuren_target file. (Later after
species_master is expanded, I can
rename it back again to 'species').
- The key column for comparison in both files is called
'species_name' in both files.
- I have already treated the tervuren_target table so that
there are no duplicate records
within itself with respect to column species_name.
- There are a couple columns I do not have to carry over to
the species_master table from
the tervuren_target table. For the columns that do have
to, I made sure
they are named exactly the same in both files and that
the collation is the same in both.
- The field names common to both the source for my data (ie.
tervuren_target) and for
the destination for the new data (species_master) are:
genus_name, species_name, authorities_species, source
- The species_master table has about 18 total fields but
except for the ones above, none of the
other fields I have not listed will receive any new data
whatsoever. They are (so to speak) just
along for the ... ride. All new data will go into the 4
fields listed above exclusively.
- In any attempt to avoid new duplicates forming as data
comes over into species_master,
at NO TIME WHATSOEVER can we allow any records in
species_master to be deleted!
The reason is because meaningful data in the other fields
in species_master would be
lost and data integrity would be violated.
Just adding ALL records from tervuren_ target was easy but it sure
did not give me the result i need. Both
the tervuren_target table and the species_master table have wood
names in them that are common to each.
As copying records over is happening, great care has to be taken to
avoid NEW duplicates forming via
the combining of records from both tables. In fact, just merging all
files from tervuren_target over into
species_master created over 3,000 NEW DUPLICATES! The result was an
unusable mess!
===> .... and that is where all the problem is that has stopped me
from being successful!
I am hoping that an SQL statement would do all the work fine .... but
if that will not work, I am also open to
adding any PHP lines for more versatility if needed.
I have made a few column name changes since approaching the group
back around May 10 but other than that,
I tried out a script tonight suggested to me back then:
SELECT DISTINCT tervuren_target.genus_name,
tervuren_target.species_name, tervuren_target.authorities_species,
tervuren_target.source
FROM tervuren_target
LEFT JOIN species_master
ON tervuren_target.species_name = species_master.species_name
LIMIT 0,12000
I had to add in the LIMIT statement or for some reason the server
would time out every time. The script ran ok
but when I looked at the number of records it created (11,377) that
was EXACTLY how many records
there are in the original Tervuren file! In other words, there was no
success in avoiding new duplicates at all
and the data did NOT actually merge into species_master <big sigh!>.
I also predicted that the DISTINCT
statement would only work on the tervuren_target table (which was
already cleaned of duplicates within itself)
and I believe I was right. it seems to be extraneous and not the
needed answer.
.... I had never thought it should be that hard just to add in new
data to a table of central important without
introducing new duplicate records --- but it is.
Therefore I come in humble request to all of you, hoping someone can
figure out what actually will work.
The solution is harder than may first seem. My email address if you
need it is billmudry at rogers.com
With thanks in advance,
Fingers crossed, toes crossed, eyeballs crossed .......
Bill Mudry
Mississauga, Ontario Canada.
--- End Message ---
