Posts by billmudry

I will work on setting up a better local WAMP environment this week. It will
take time to organize .... along with a complete copy of the present website.
The sight with code, data, scans and photos has got quite big now.

Unless someone else has any other code solutions to propose, should I consider
closing this thread or leave it open in case someone does have a suggestion
later? I could always start a new thread later.

hmmm. Guess it wouldn't hurt to ask if any of you have opinions on XAMMP or
Easy PHP. I would rather use some package that pre-configures Apache, PHP and
MySQL. I am not about to learn the intricate details of compiling and linking
all of these together.

Much thanks,

Bill

What I can suggest is.
1) Download database copy from live server.
2) Set up database in your local pc in mysql.
3) Note the last record in your main table where you want to add 670 rows.
4) Now run script locally and insert new records in local database (copy of live).
5) export new records to sql script (simple insert command)
6) run script of new records on live server

If you are not phased by installing MySQL (and optionally PHP) on your own computer, you might like to consider creating a mirror image of the database so that you can manage your own user's in MySQL and set the permissions as required. You can then (hopefully) create scripts from your web-based database to re-create it on your own computer. It's a long slog to prove a point.

In past years, I have installed XAMMP. I found, however, that the results I got were not as accurate as doing it on my hosting site. Its been a few
years, so I can't recall the things that did not work right. Perhaps I should update to the latest XAMMP. I should possibly consider installing Easy PHP to compare. I have seen in the past how trying to configure Apache, PHP and MySQL can get quite involved if done manually. I would rather not have to. AHH.... I remember one thing. I got some virus on my local machine that just loved targeting XAMMP or other PHP programs. Hopefully that is long gone.

You have a point that I then would be able to check if I wrote a script properly
---- independent of the unreasonable restrictions from GoDaddy. Since some pages
rely on others, it would mean eventually making sure that both the local and
online sites were both kept updated. I have to admit that I really like how
Dreamweaver saves each change so easily so I don't have to ftp changes.

Secondly, I'm not quite sure about the restrictions your provider has set on the INSERT INTO permission. Is it only the SELECT INTO clause that is prohibited or is

As I wrote, when the SELECT statement is run separately, it works like a charm,
separating the left halves of all the species and listing them --- the genera data
that I need. It has to be the INSERT statement ..... and confirmed by the techy I
talked to.

it just that you can't perform any scripted INSERT statements? If it's the latter, then how does data get into the table in the first place? Do you have to use a web form to do that?
Would you (for example) be able to create a script, from your original select statement, that would have one line for each INSERT statement that you need? By this I mean creating a script with a series of

INSERT INTO sci_genera (genus_name) VALUES ('genus_name1');
INSERT INTO sci_genera (genus_name) VALUES ('genus_name2');
INSERT INTO sci_genera (genus_name) VALUES ('genus_name3');

type statements.
Would you be allowed to do that?

Whether or not they would allow that is not the question because the number of records (genus names) - a large 679 -- that I need to add to the genus name column in the genera. If I had to set that up, it would be much slower than just running the SELECT statement in phpMyAdmin to show the required names and then using MySQL-FRONT to manually enter each one manually anyway.

MySQL-FRONT, by the way, is fantastic to use when you have a lot of manual data
manipulation to do. I doubt there is any other program like it. With others you
have to use a form for each record. MySQL-FRONT instead works very much like a
spreadsheet. Each cell is active so you just have to enter a value and hit <ENTER>
or just move over to an other cell ---- and it automatically saves(!) :-). It has
saved me hours and hours of time.

It is a sad shame that the author has dropped developing it. I wish I could have got a registered copy. If someone has a copy that can be shared (or at least a key)
please be in touch.
table.

Rodney (and others), Here is what I came up with. First, the SELECT statement
below run by itself worked just fine, bringing forth a list of the 679 genera names, just what I need to copy and insert into another table, The sci_genera table, column genus_name.

SELECT DISTINCT 
left(species_name,locate(" ", `species_name`)-1) 
FROM `species` 
WHERE `generaID`=0
ORDER BY `genus_name`

The second line takes the species names which are always two part......
[genus] (space) [epithet]
and separates all the genera (genus is singular, genera is plural) in a nice
vertical list. The epithet on the right can be discarded. The 'generaID equals
zero' makes sure the only records kept is the missing ones in sci_genera.genus_name.
As you can appreciate, it would be a lot of work to manually add 679 records
each manually if a script can do it.

The DISTINCT statement makes sure the genus names are not repeated.
Since the script properly generates the genera list of missing data, all I should
have to do is insert/append all of this data into sci_genera.genus_name .... job then done. I added an INSERT statement to try doing that:

INSERT INTO sci_genera.genus_name
(
SELECT DISTINCT 
left(species_name,locate(" ", `species_name`)-1) 
FROM `species` 
WHERE `generaID`=0
ORDER BY `genus_name`
LIMIT 0 , 5000
)

The LIMIT statement is only because I am using PHP that will put a useless,
interfering value automatically if I do not. (By the way, does anyone know how to
turn off that feature in phpMyAdmin?)

I got an unexpected result. No syntax type error was reported. Instead it posted:

#1142 - INSERT command denied to user 'taxa@'(ip confidential)" [host name] for
table 'genus_name"

I phoned GoDaddy where my hosting is and had a long talk with a technician. I was
so shocked to find that they will not allow something as basic as an INSERT INTO
command! I might yet decide I need a different hosting company! Incredible. When
I tried to find on their site how I could open up permissions on 'taxa', the techy
said there was NONE(!). In fact, he would not/could not even tell me the permissions that GoDaddy has set permissions to on 'taxa'! <incredible!>.

So, there are two things I would like to ask.

1) Could you please look at that script and tell me if it is written properly
such that (if it wasn't GoDaddy messing up things) the script should run
properly on a system that allows INSET INTO to run. They said I could run
anything I want by getting a partitioned virtual account from them, but at
$30/month, that is an expense I would hope to prevent.

2) IF not this method, how can I otherwise append this list of missing genera
into sci_genera.genus_name? (UPDATE? What else?) If you could write that
part, I will gladly try it out.

I would rather use the power of SQL than to manually edit-in almost 700 records. LOL

Bill

====================================================================
co

Hello,

I am not sure exactly what fields you are trying to post to the second file but here is the way i normally do what you are attempting.

Step 1. Figure out the record ID or something unique you can use to select the records needed for your insert.

select table1.ID 
from table1
where IS_In_table2 is null

Step 2. Create a select statement that generates output in the order and format needed for the insert into the new table. i.e. if I want to insert into table2 fields table2.f1, table2.f2, table2.f3, table2.f4, and table2.f5 in that order. In table one the corresponding fields are table1.f3, table1.f5, table1.f7, table1.f9, and table1.f15 then my query from would be:

select table1.f3, table1.f5, table1.f7, table1.f9, table1.f15
from table1

Step 3. Merge the two to get the data you need to insert:

select table1.f3, table1.f5, table1.f7, table1.f9, table1.f15
from table1
where table1.ID in (select table1.ID 
from table1
where IS_In_table2 is null)

Step 4. add the insert statement to the query:

Insert into table2 (f1, f2, f3, f4,f5)
select table1.f3, table1.f5, table1.f7, table1.f9, table1.f15
from table1
where table1.ID in (select table1.ID 
from table1
where IS_In_table2 is null)

I hope this helps.

I while ago I rethought the architecture on this site, dropping the idea of using
pop-up windows. That simplified coding a lot! I now have successfully working 3
look ahead functions :-). Job done!

Could someone please remind me of how I can close this thread. I don't remember how.

After a few months working on another website, I am back finishing a list of coding
tasks on my TAXA Wood Knowledge Base. One of the most important pages in the site is the one that lists (so far) over 15,000 different botanical names of woods.

Each single name listing is also linked to the corresponding data page for that wood where details are given on each wood. On such data pages are photo scans of as many woods as I can supply (So far about 2,100 out of the 15,000 listed). Rather than cluttering each page with more information than many readers may want, I have three pop-up pages to show more colour photos,UV fluorescence photos and photomicro photos under the microscope.

With only that, readers looking at the large list of species would find all these scans and photos by coming to them randomly with many species having none. To make this far easier to actually pick which woods have scans, uv photos and so on I am adding "look ahead" icons. If a listed wood has any of these categories have any of these scans on their data pages or on the pop-up pages, there is or will be an icon beside the botanical name to make it easy to realize there is and then visit those.

If any of this seems too complex to understand right away, the site is at
www.woodsoftheworld.org under the menu item Woody Species. To drop right out of the frames environment directly, the page is directly at www.woodsoftheworld.org/alphaspecies.php.

The color photos are showing fine for a few months already as are the camera icons
beside the species names for some months now. I have about 100 UV fluorescence photos that are also showing off ok but I am now working on placing a small circle with a "UV" in it for all UV photos on woods that do shine under UV. That is where I have come up with more difficulties than I ever expected.

Now at least I hope this is enough to illustrate what I am trying to do. One reason it was easy to get the camera icon working well is because the field where addresses to pick up the camera icon as needed is in the same 'species' file as is needed to store all basic information on each species. After that file grew to at least 18 fields, I thought that maybe I should finally pay some attention to some normalization and store the UV photo addresses in its own table of 'uvphotos'. It has only 4 fields (uvID, speciesID, species_name and uv_filename.

The 15,000 names are extracted from the species table and displayed with a while loop. Each time through that loop it posts the next species name, so I found it necessary to run a SELECT query within that loop to find if there is any UV photo address per listed species and to echo out the UV icon if there is. Because these photo addresses are stored in a separate table, I had to use a JOIN in the SELECT STATEMENT. To test for the right statement since you cannot use $variables in MySQL like you can in PHP, I chose a species name that I knew did have a uv photo.
I found that the query that would find the one unique address to use is:

$uvquery = 'SELECT u.uv_filename 
		FROM `uvphotos` u 
		JOIN `species` s ON u.species_name=s.species_name 
		WHERE u.species_name="$species_name"';

First, then, this ran without any error reported and listed all the species that
do have uv photos for which the uv icon should show. By substituting the $species_name with one that really does have a uv photo such as Acacia aneura,the query does properly bring forth the address where the photo is stored.

In the page (alphaspecies.php), the $uvquery runs fine without errors reporting and so does $uvresult = mysql_query($uvquery). Running .....
$uvrow=mysql_fetch_array($uvresult, MYSQL_ASSOC); should work ok, too but
when I try to use extract($uvrow) it constantly complains that it cannot find a valid array to expand. Doing a row count shows that there are no rows in the query results even though they list beautifully running the query in phpmyadmin.

The code I have now that I will paste below runs ok without reported errors but a debug statement reports "No uv photo" both for the ones that do not have one AND for those that do! <argh!>. I cannot find why it does not pick up the existence of a uv photo where they do exist. It should be posting a uv icon beside each species that does have one.

Any code that is related on the issue starts at about line 180 or so. Here, then is the code for the whole page. You will find quite a few commented out lines that were used just to understand and try to debug what was going on.

================================================================================
<code:php>


<?php
	session_start();

	//$_SESSION['$s'] = "speciesname";

error_reporting(-1); // Report every single error going.
ini_set('display_errors', 1); // Show them in-line.
set_time_limit(0); // No time limit.

?>

<HEAD>
	<TITLE>TAXA: Display of species by alphabetical groups</TITLE>
    
    <STYLE type="text/css">
    	A:link { text-decoration: none }
		A:active { text-decoration: none }
		A:visited { text-decoration: none }
	</STYLE>
    
</HEAD>

<BODY>

<?php
//////////////////////////////////////////////////////////////////////////////////////
//  File: alphaspecies.php
//  Date last revision: June 17, 2010
//  Description: This file offers the user to pick an alphabet that limits the species
//			shown to those having botanical namss starting with that alphabet and
//  		displays all the data and picture(s) on one chosen species
//		It also will hold the icons that look ahead to let viewers know if each species 
//		has a scan picture, additional pictures, a UV fluoresence picture and/or a 
//		photomicro photo
//
//
//
//
//
//
//
//////////////////////////////////////////////////////////////////////////////////////

//$speciesname = "";
//$letter = "";
$uv_filename = "";
//rowcount = "";

// Assuming your file name is "index.php"; set up the base link for all pages

$baseLink = "alphaspecies.php?letter="; 

////////////////////////////////////////////////////////////////////////////////////////
//                 CONNECT TO THE DATABASE
////////////////////////////////////////////////////////////////////////////////////////

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");

////////////////////////////////////////////////////////////////////////////////////////
//                 HOPEFULLY CONNECTED TO THE DATABASE
////////////////////////////////////////////////////////////////////////////////////////


////////////////////////////////////////////////////////////////////////////////////////
//
//  This section sets up a choice of alphabet for users to pick to cut down on the volume
//  of species data. 
//
////////////////////////////////////////////////////////////////////////////////////////

// SET UP A LETTER DISPLAY AND PICKING AREA.
echo "<table border='3'cellpadding='20' bgcolor='ivory' align='center'><tr><td valign='top'>";
//$letter=="";
// Printing alphabet with links
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";
	}
$letter = "";
@$letter = $_GET["letter"];

//echo "\$letter on line 27 is - $letter.<BR /> ";

echo "<br />";
echo '<div ID="letterbox" align="center">';
	if (isset($_GET["letter"]) && $_GET["letter"] != "")
		{
		Echo "<font color='888'>You picked letter $letter <br /></font>";
		}
	  else {
		Echo "Please choose a letter <br />";  
		};
echo "</div>";

//$letter = $_GET["letter"];
//Echo "\$letter is still readable as $letter <br />";

// Close off the display alphabet box
echo "<br></td></tr></table><br />";  

////////////////////////////////////////////////////////////////////////////////////////
//                END OF ALPHABET CHOOSING AREA
////////////////////////////////////////////////////////////////////////////////////////


// Now pick up a user chosen alphabet

////////////////////////////////////////////////////////////////////////////////////////
//  --------------------------- START OF MAIN DISPLAY TABLE -----------------------   //
////////////////////////////////////////////////////////////////////////////////////////
echo "<table border='3'cellpadding='20' bgcolor='ivory' align='center'><tr>";
echo "<td valign='top' style='white-space: nowrap'>";


///////////////////////////////////////////////////////////////////////////////////////
// Now that the user has chosen a letter, go get the species starting with that letter
///////////////////////////////////////////////////////////////////////////////////////
   $alphaquery = "SELECT * FROM species WHERE species_name LIKE '".$letter."%' order by species_name"; // 
   //Echo "Line 124 \$species_name is - $species_name<br>\n";

 $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();

///////////////////////////////////////////////////////////////////////////////////////
// Set up the query to look ahead whether or not the present species has a UV picture
///////////////////////////////////////////////////////////////////////////////////////




/*
	$numrows = mysql_num_rows($uvresult) or die (mysql_error());
	//ECHO "Number of rows is - $numrows<br />";

	

	//$row = mysql_fetch_array($uvresult);
	//ECHO "\$uvrow on line 104 is - $uvrow<BR />";
		
		
	//ECHO "The path and file name, line 109 is $uvrow('uv_filename')<BR />";
	

*/


$l=0;
$m=1;
$n=0;


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.
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////

// Set up image name and path for showing a camera icon for species that have a scan picture
$camera_icon = "images/cameraicon01.jpg";
$showcamera = "<img src = $camera_icon>";

// Set up image name and path for showing a UV icon for species that have a UV fluorescence photo
$uv_icon = "images/uv.gif";
$showuv =  "<img src = $uv_icon>";

/////////////////////////////////////////////////////////////////////////////////////////////////////////
//
//	LOOPING THROUGH SPECIES STARTS HERE
//
/////////////////////////////////////////////////////////////////////////////////////////////////////////

	//var_dump($result5);

if($result5) 
	{
    while($row5 = mysql_fetch_array($result5))
		{
        extract($row5);
		

   //Echo "Line 206 \$species_name is - $species_name<br>\n";
   
   //$species_name = 'Acacia aneura'; //Debug statement to set species_name temporarily
   

/////////////////////////////////////////////////////////////////////////////////////////////////////////
//		UV Lookahead section starts
/////////////////////////////////////////////////////////////////////////////////////////////////////////

   	$uvquery = 'SELECT u.uv_filename 
		FROM `uvphotos` u 
		JOIN `species` s ON u.species_name=s.species_name 
		WHERE u.species_name="$species_name"';

		//Echo "\$speciesname at line 215 is $species_name"; Debug statement

		$uvresult = mysql_query($uvquery) 
		or die(mysql_error());

		//var_dump($uvresult);
		//Echo "<br />";

	$uvrow=mysql_fetch_array($uvresult, MYSQL_ASSOC);
	//extract($uvrow);
			//Echo "Line 226 - \$uv_filename is - $uv_filename<br />";



	$rowcount = mysql_num_rows($uvresult);
		//var_dump($rowcount);
		//Echo "<br />";


	//Echo "Number of UV rows, line 235 is $rowcount<br />";

   	//if(mysql_num_rows($uvresult) > 0){
		//$uvrow = mysql_fetch_array($uvresult, MYSQL_ASSOC);

		//ECHO "\$uvrow on line 237 is - $uvrow<BR />";
		
	};

	//$uv_filename = $uvrow['uv_filename'];

	//ECHO "\$uvrow on line 240 is - $uvrow<BR />";

/////////////////////////////////////////////////////////////////////////////////////////////////////////
//		UV Lookahead section ends
/////////////////////////////////////////////////////////////////////////////////////////////////////////

		$l++; // Increment 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);

			
		$uv_filename = trim($uv_filename);


		echo '<a href="displayspecies.php?&species_name='.$species_name.' ">';
		echo "<font color=grey>$m</font> - $species_name";
		echo"</a>.";
		
		

		if ($picture_filename1) 
		   
			echo "$showcamera";	
				// the only time it should show a camera icon
			
		//echo "<br />\$uv_filename on line 261 is - $uv_filename";
		
		//echo "Line 263 - The UV filename is $uv_filename<br />";

	

	if ($uv_filename) 
		   {
			echo $showuv;	// the only time it should show a UV icon
			};	

		echo "<br>\n";
		 //ECHO "$showuv"; // hard coded test to place UV icon on all

	/*
		*/
		
		$m++; // counter to increment for the next species
		$n++; // counter to increment to the next uv photo

		}
    else echo "No species found";

	//}
////////////////////////////////////////////////////////////////////////////////////////////////////    
//
//                              END OF DISPLAY LOOP
//  
////////////////////////////////////////////////////////////////////////////////////////////////////    
//Echo "species_name is - $species_name<br>\n";

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>";

/////////////////////////////////////////////////////////////////////////////////////////////
// ----------------------------- Total End of Species Listing -------------------------------
/////////////////////////////////////////////////////////////////////////////////////////////

	$letter="";
	//$species_name="";
	$uv_filename="";
	


?>`

<!-- -------------------- The following script is needed by GoDaddy for tracking stats on traffic ---------------->

<SCRIPT type="text/JavaScript">
var TFN='';var TFA='';var TFI='0';var TFL='0';var tf_RetServer="rt.trafficfacts.com";var tf_SiteId="44ge27e3f31f6297ededc5ee9d3355117dbc06520fah26";var tf_ScrServer=document.location.protocol+"//rt.trafficfacts.com/tf.php?k=44ge27e3f31f6297ededc5ee9d3355117dbc06520fah26;c=s;v=5";document.write(unescape('%3Cscript type="text/JavaScript" src="'+tf_ScrServer+'">%3C/script>'));
</SCRIPT>

<NOSCRIPT><IMG src="http://rt.trafficfacts.com/ns.php?k=44ge27e3f31f6297ededc5ee9d3355117dbc06520fah26" height="1" width="1" alt=""/></NOSCRIPT>

</BODY>
</HTML>

</code>

================================================================================

running the query:

SELECT u.uv_filename
FROM uvphotos u
JOIN species s ON u.species_name = s.species_name
WHERE u.species_name = "Acacia aneura"

produces the proper answer only the uv_filename field of
uva/acacia_aneura-mulgaUV02.jpg

At this point, I am stumped to understand why the code I have written does not successfully bring forth all the photo addresses where they do exist amidst the
15,000 species reported. You can view this right at the output page.

Once this is fixed, it will be much easier to complete the look aheads for the remaining photomicro and more photos areas --- and a lot closer to finally finishing all the coding for what I planned to be version 1 of the website.
As always, I am indebted and thankful for your help.

Bill Mudry
Mississauga, Canada.

Rodney, your suggestions sound good. Just thought I would add a short acknowledgement that I read your post ... before I go try some of it --- so you know you are not ignored :-) .

Bill

Hello,

I am not sure exactly what fields you are trying to post to the second file but here is the way i normally do what you are attempting.

Step 1. Figure out the record ID or something unique you can use to select the records needed for your insert.

select table1.ID 
from table1
where IS_In_table2 is null

Step 2. Create a select statement that generates output in the order and format needed for the insert into the new table. i.e. if I want to insert into table2 fields table2.f1, table2.f2, table2.f3, table2.f4, and table2.f5 in that order. In table one the corresponding fields are table1.f3, table1.f5, table1.f7, table1.f9, and table1.f15 then my query from would be:

select table1.f3, table1.f5, table1.f7, table1.f9, table1.f15
from table1

Step 3. Merge the two to get the data you need to insert:

select table1.f3, table1.f5, table1.f7, table1.f9, table1.f15
from table1
where table1.ID in (select table1.ID 
from table1
where IS_In_table2 is null)

Step 4. add the insert statement to the query:

Insert into table2 (f1, f2, f3, f4,f5)
select table1.f3, table1.f5, table1.f7, table1.f9, table1.f15
from table1
where table1.ID in (select table1.ID 
from table1
where IS_In_table2 is null)

I hope this helps.

Before I really dig in ----- HAPPY NEW YEAR EVERYONE!

A few of you may remember that I have been working on producing a large wood knowledge base (woodsoftheworld.org). It is totally none profit. I am getting much closer to having enough working that soon I hope to be able to declare it at version 1 stage.

I have a large respect on how powerful SQL can be but I still have difficulties to
get my statements right, undoubtedly syntax errors. Hope someone can help with
something a few may look at as elementary. This year I want to get better at writing successful statements. Some things are hard to find exactly what a person is looking for in books or online.
--------------------------

Back months ago, I was able to get a large list of existing woods (species) from the second largest wood collection in the world, the Tervuren Xylarium in the Netherlands, over 17,000 records. I record all botanical names of wood in the 'species' table. All tables used for the project are in the 'TAXA" database.

With the gracious help from others, I was able to import the refined Tervuren list without producing a huge number of duplicates, the new total being just over 15,000 woody species. That was a huge jump in the success of the project!

In fact, I have since proven that the species brought in were so numerous that the
genus table is now missing 669 new genera (genera is plural) that are in the species file.

For those that perhaps do not know, botanical species names are made of two parts, the genus and the epithet. For instance, White Oak has a botanical name of
'Quercus alba'. The 'Quercus' is the genus name, so even if there are some missing
genera, they can be generated out of the species name. The following script does that plus shows that all missing genera in the sci_genera table have no index values .. to be expected at this stage. So far, I have been able to list all the missing genera with this script:

<code>
SELECT DISTINCT
left(species_name,locate(" ", `species_name`)-1) ,`generaID`
FROM `species`
WHERE `generaID`=0
ORDER BY `genus_name`
LIMIT 0 , 5000
</code>

Now that I can have a list of all NEW species not yet reported in the genus table (sci_genera), I need to copy all of these into the genus table so it will be complete. I will expect that the insertion process will automatically generate the
generaID values.

I have tried different things but without required results. Last night when I tried to use a 'INSERT" style of script, it came disappointingly back gave an error saying the two files were of different lengths (end of effort). Now that leaves me wondering whether the best approach is the use "INSERT", 'UPDATE, 'ALTER'(less likely) some other command, or if there is an APPEND command good to use.

If I was to generalize the needed procedure, I need to copy over to a column in one table missing data that is embodied in another column in another table. ...... So, what is the best approach to do this? The parameters that should be needed include:

'species' - The table where all species are listed.
'species_name" - The column where all species are stored.
'sci_genera' - The table where the genera names are stored.
'genus_name' - The column where the genera names are stored ((and the target to
append the derived genera from the species listing.

Remember .... you cannot equate anything to the genus prime key (generaID) because they are not yet generated for the missing genera. I especially look forward to getting a working SQL solution :-).

(Later, once the new generaID values get generated, I will want to copy them back into the species table as a foreign key --- but lets not risk confusing things. That can wait for another day).

Much thanks. My efforts in creating the TAXA Wood Knowledge Base site would not be possible without the help of the programming forums I use.

Bill Mudry
Mississauga, Ontario Canada

After a few months working on another website, I am back finishing a list of coding
tasks on my TAXA Wood Knowledge Base. One of the most important pages in the site is the one that lists (so far) over 15,000 different botanical names of woods.

Each single name listing is also linked to the corresponding data page for that wood where details are given on each wood. On such data pages are photo scans of as many woods as I can supply (So far about 2,100 out of the 15,000 listed). Rather than cluttering each page with more information than many readers may want, I have three pop-up pages to show more colour photos,UV fluorescence photos and photomicro photos under the microscope.

With only that, readers looking at the large list of species would find all these scans and photos by coming to them randomly with many species having none. To make this far easier to actually pick which woods have scans, uv photos and so on I am adding "look ahead" icons. If a listed wood has any of these categories have any of these scans on their data pages or on the pop-up pages, there is or will be an icon beside the botanical name to make it easy to realize there is and then visit those.

If any of this seems too complex to understand right away, the site is at
www.woodsoftheworld.org under the menu item Woody Species. To drop right out of the frames environment directly, the page is directly at www.woodsoftheworld.org/alphaspecies.php.

The color photos are showing fine for a few months already as are the camera icons
beside the species names for some months now. I have about 100 UV fluorescence photos that are also showing off ok but I am now working on placing a small circle with a "UV" in it for all UV photos on woods that do shine under UV. That is where I have come up with more difficulties than I ever expected.

Now at least I hope this is enough to illustrate what I am trying to do. One reason it was easy to get the camera icon working well is because the field where addresses to pick up the camera icon as needed is in the same 'species' file as is needed to store all basic information on each species. After that file grew to at least 18 fields, I thought that maybe I should finally pay some attention to some normalization and store the UV photo addresses in its own table of 'uvphotos'. It has only 4 fields (uvID, speciesID, species_name and uv_filename.

The 15,000 names are extracted from the species table and displayed with a while loop. Each time through that loop it posts the next species name, so I found it necessary to run a SELECT query within that loop to find if there is any UV photo address per listed species and to echo out the UV icon if there is. Because these photo addresses are stored in a separate table, I had to use a JOIN in the SELECT STATEMENT. To test for the right statement since you cannot use $variables in MySQL like you can in PHP, I chose a species name that I knew did have a uv photo.
I found that the query that would find the one unique address to use is:

$uvquery = 'SELECT u.uv_filename 
		FROM `uvphotos` u 
		JOIN `species` s ON u.species_name=s.species_name 
		WHERE u.species_name="$species_name"';

First, then, this ran without any error reported and listed all the species that
do have uv photos for which the uv icon should show. By substituting the $species_name with one that really does have a uv photo such as Acacia aneura,the query does properly bring forth the address where the photo is stored.

In the page (alphaspecies.php), the $uvquery runs fine without errors reporting and so does $uvresult = mysql_query($uvquery). Running .....
$uvrow=mysql_fetch_array($uvresult, MYSQL_ASSOC); should work ok, too but
when I try to use extract($uvrow) it constantly complains that it cannot find a valid array to expand. Doing a row count shows that there are no rows in the query results even though they list beautifully running the query in phpmyadmin.

The code I have now that I will paste below runs ok without reported errors but a debug statement reports "No uv photo" both for the ones that do not have one AND for those that do! <argh!>. I cannot find why it does not pick up the existence of a uv photo where they do exist. It should be posting a uv icon beside each species that does have one.

Any code that is related on the issue starts at about line 180 or so. Here, then is the code for the whole page. You will find quite a few commented out lines that were used just to understand and try to debug what was going on.

================================================================================
<code:php>


<?php
	session_start();

	//$_SESSION['$s'] = "speciesname";

error_reporting(-1); // Report every single error going.
ini_set('display_errors', 1); // Show them in-line.
set_time_limit(0); // No time limit.

?>

<HEAD>
	<TITLE>TAXA: Display of species by alphabetical groups</TITLE>
    
    <STYLE type="text/css">
    	A:link { text-decoration: none }
		A:active { text-decoration: none }
		A:visited { text-decoration: none }
	</STYLE>
    
</HEAD>

<BODY>

<?php
//////////////////////////////////////////////////////////////////////////////////////
//  File: alphaspecies.php
//  Date last revision: June 17, 2010
//  Description: This file offers the user to pick an alphabet that limits the species
//			shown to those having botanical namss starting with that alphabet and
//  		displays all the data and picture(s) on one chosen species
//		It also will hold the icons that look ahead to let viewers know if each species 
//		has a scan picture, additional pictures, a UV fluoresence picture and/or a 
//		photomicro photo
//
//
//
//
//
//
//
//////////////////////////////////////////////////////////////////////////////////////

//$speciesname = "";
//$letter = "";
$uv_filename = "";
//rowcount = "";

// Assuming your file name is "index.php"; set up the base link for all pages

$baseLink = "alphaspecies.php?letter="; 

////////////////////////////////////////////////////////////////////////////////////////
//                 CONNECT TO THE DATABASE
////////////////////////////////////////////////////////////////////////////////////////

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");

////////////////////////////////////////////////////////////////////////////////////////
//                 HOPEFULLY CONNECTED TO THE DATABASE
////////////////////////////////////////////////////////////////////////////////////////


////////////////////////////////////////////////////////////////////////////////////////
//
//  This section sets up a choice of alphabet for users to pick to cut down on the volume
//  of species data. 
//
////////////////////////////////////////////////////////////////////////////////////////

// SET UP A LETTER DISPLAY AND PICKING AREA.
echo "<table border='3'cellpadding='20' bgcolor='ivory' align='center'><tr><td valign='top'>";
//$letter=="";
// Printing alphabet with links
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";
	}
$letter = "";
@$letter = $_GET["letter"];

//echo "\$letter on line 27 is - $letter.<BR /> ";

echo "<br />";
echo '<div ID="letterbox" align="center">';
	if (isset($_GET["letter"]) && $_GET["letter"] != "")
		{
		Echo "<font color='888'>You picked letter $letter <br /></font>";
		}
	  else {
		Echo "Please choose a letter <br />";  
		};
echo "</div>";

//$letter = $_GET["letter"];
//Echo "\$letter is still readable as $letter <br />";

// Close off the display alphabet box
echo "<br></td></tr></table><br />";  

////////////////////////////////////////////////////////////////////////////////////////
//                END OF ALPHABET CHOOSING AREA
////////////////////////////////////////////////////////////////////////////////////////


// Now pick up a user chosen alphabet

////////////////////////////////////////////////////////////////////////////////////////
//  --------------------------- START OF MAIN DISPLAY TABLE -----------------------   //
////////////////////////////////////////////////////////////////////////////////////////
echo "<table border='3'cellpadding='20' bgcolor='ivory' align='center'><tr>";
echo "<td valign='top' style='white-space: nowrap'>";


///////////////////////////////////////////////////////////////////////////////////////
// Now that the user has chosen a letter, go get the species starting with that letter
///////////////////////////////////////////////////////////////////////////////////////
   $alphaquery = "SELECT * FROM species WHERE species_name LIKE '".$letter."%' order by species_name"; // 
   //Echo "Line 124 \$species_name is - $species_name<br>\n";

 $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();

///////////////////////////////////////////////////////////////////////////////////////
// Set up the query to look ahead whether or not the present species has a UV picture
///////////////////////////////////////////////////////////////////////////////////////




/*
	$numrows = mysql_num_rows($uvresult) or die (mysql_error());
	//ECHO "Number of rows is - $numrows<br />";

	

	//$row = mysql_fetch_array($uvresult);
	//ECHO "\$uvrow on line 104 is - $uvrow<BR />";
		
		
	//ECHO "The path and file name, line 109 is $uvrow('uv_filename')<BR />";
	

*/


$l=0;
$m=1;
$n=0;


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.
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////

// Set up image name and path for showing a camera icon for species that have a scan picture
$camera_icon = "images/cameraicon01.jpg";
$showcamera = "<img src = $camera_icon>";

// Set up image name and path for showing a UV icon for species that have a UV fluorescence photo
$uv_icon = "images/uv.gif";
$showuv =  "<img src = $uv_icon>";

/////////////////////////////////////////////////////////////////////////////////////////////////////////
//
//	LOOPING THROUGH SPECIES STARTS HERE
//
/////////////////////////////////////////////////////////////////////////////////////////////////////////

	//var_dump($result5);

if($result5) 
	{
    while($row5 = mysql_fetch_array($result5))
		{
        extract($row5);
		

   //Echo "Line 206 \$species_name is - $species_name<br>\n";
   
   //$species_name = 'Acacia aneura'; //Debug statement to set species_name temporarily
   

/////////////////////////////////////////////////////////////////////////////////////////////////////////
//		UV Lookahead section starts
/////////////////////////////////////////////////////////////////////////////////////////////////////////

   	$uvquery = 'SELECT u.uv_filename 
		FROM `uvphotos` u 
		JOIN `species` s ON u.species_name=s.species_name 
		WHERE u.species_name="$species_name"';

		//Echo "\$speciesname at line 215 is $species_name"; Debug statement

		$uvresult = mysql_query($uvquery) 
		or die(mysql_error());

		//var_dump($uvresult);
		//Echo "<br />";

	$uvrow=mysql_fetch_array($uvresult, MYSQL_ASSOC);
	//extract($uvrow);
			//Echo "Line 226 - \$uv_filename is - $uv_filename<br />";



	$rowcount = mysql_num_rows($uvresult);
		//var_dump($rowcount);
		//Echo "<br />";


	//Echo "Number of UV rows, line 235 is $rowcount<br />";

   	//if(mysql_num_rows($uvresult) > 0){
		//$uvrow = mysql_fetch_array($uvresult, MYSQL_ASSOC);

		//ECHO "\$uvrow on line 237 is - $uvrow<BR />";
		
	};

	//$uv_filename = $uvrow['uv_filename'];

	//ECHO "\$uvrow on line 240 is - $uvrow<BR />";

/////////////////////////////////////////////////////////////////////////////////////////////////////////
//		UV Lookahead section ends
/////////////////////////////////////////////////////////////////////////////////////////////////////////

		$l++; // Increment 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);

			
		$uv_filename = trim($uv_filename);


		echo '<a href="displayspecies.php?&species_name='.$species_name.' ">';
		echo "<font color=grey>$m</font> - $species_name";
		echo"</a>.";
		
		

		if ($picture_filename1) 
		   
			echo "$showcamera";	
				// the only time it should show a camera icon
			
		//echo "<br />\$uv_filename on line 261 is - $uv_filename";
		
		//echo "Line 263 - The UV filename is $uv_filename<br />";

	

	if ($uv_filename) 
		   {
			echo $showuv;	// the only time it should show a UV icon
			};	

		echo "<br>\n";
		 //ECHO "$showuv"; // hard coded test to place UV icon on all

	/*
		*/
		
		$m++; // counter to increment for the next species
		$n++; // counter to increment to the next uv photo

		}
    else echo "No species found";

	//}
////////////////////////////////////////////////////////////////////////////////////////////////////    
//
//                              END OF DISPLAY LOOP
//  
////////////////////////////////////////////////////////////////////////////////////////////////////    
//Echo "species_name is - $species_name<br>\n";

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>";

/////////////////////////////////////////////////////////////////////////////////////////////
// ----------------------------- Total End of Species Listing -------------------------------
/////////////////////////////////////////////////////////////////////////////////////////////

	$letter="";
	//$species_name="";
	$uv_filename="";
	


?>`

<!-- -------------------- The following script is needed by GoDaddy for tracking stats on traffic ---------------->

<SCRIPT type="text/JavaScript">
var TFN='';var TFA='';var TFI='0';var TFL='0';var tf_RetServer="rt.trafficfacts.com";var tf_SiteId="44ge27e3f31f6297ededc5ee9d3355117dbc06520fah26";var tf_ScrServer=document.location.protocol+"//rt.trafficfacts.com/tf.php?k=44ge27e3f31f6297ededc5ee9d3355117dbc06520fah26;c=s;v=5";document.write(unescape('%3Cscript type="text/JavaScript" src="'+tf_ScrServer+'">%3C/script>'));
</SCRIPT>

<NOSCRIPT><IMG src="http://rt.trafficfacts.com/ns.php?k=44ge27e3f31f6297ededc5ee9d3355117dbc06520fah26" height="1" width="1" alt=""/></NOSCRIPT>

</BODY>
</HTML>

</code>

================================================================================

running the query:

SELECT u.uv_filename
FROM uvphotos u
JOIN species s ON u.species_name = s.species_name
WHERE u.species_name = "Acacia aneura"

produces the proper answer only the uv_filename field of
uva/acacia_aneura-mulgaUV02.jpg

At this point, I am stumped to understand why the code I have written does not successfully bring forth all the photo addresses where they do exist amidst the
15,000 species reported. You can view this right at the output page.

Once this is fixed, it will be much easier to complete the look aheads for the remaining photomicro and more photos areas --- and a lot closer to finally finishing all the coding for what I planned to be version 1 of the website.
As always, I am indebted and thankful for your help.

Bill Mudry
Mississauga, Canada.

I found out some years ago that some woods will actually fluoresce under UV light and that I can photograph that glow. In fact, I estimate over 10$ of the 3,000+ actual wood species samples I have glow under black light.

I have worked on for some time the TAXA Wood Knowledge Base (www.prowebcanada.com/taxa) that reports the existence and information on over 15,000 different wood species. When you pick a certain wood, you are taken to the data page for that wood. On that page (amongst other things) are three pop-up windows. One of them is to display any UV photos I have so far of woods that do glow.

The odd thing about how it is working is that some photos (all JPEG's) show but some do not and I don't know why. All pages with data are dynamically run with PHP and stored in MySQL tables. As a check against corrupt photo files, I have run the ones that do not show in phpAdmin under 'Execute'---- and they showed up fine. If they all didn't show, of course, it would be easy to see that there was a code problem but via the same code, over 2/3 show. Oddly, some critical error is not happening since with the photos that do not show, the page even seems to crash after the first words echos from code.

I will include the full page code. All photos for now are stored under ./uv/uva.
File names and paths are kept in the Taxa database, 'uvphotos' table, 'uvfilename' column.

This one has me stumped. I hope someone has some idea what is causing this.

Bill Mudry
Mississauga,Ontario (near Toronto)

=============================================================================

<?php
////////////////////////////////////////////////////////////////////////////////////////////////////////
//  Program: show_uv_photos.php
//  Description: This program displays Uv fluorescence photos of wood found to glow under an ultraviolet
//  light. This is around 10% - 15% of all wood species. 
//
//  It runs as a pop-up window and is activated by a button titled "U.V. Fluorescence" generated by 
//  displayspecies.php. Future code may be added later to display the buttons only if the program can
//  detect UV photos stored for each species.
//  PARAMETERS:
//      {$_SESSION["speciesname"]} - Looks back to find what the current species picked is.
//		speciesname - The session variable name for the current species.
//		$speciesname - The standard variable name taken from the session variable.	
//		species_name - the name of the column that stores up to over 15,000 different botanical names of wood.
//		uvphotos - the name of the table in the TAXA database that holds the partial path and photo file
//			name
////////////////////////////////////////////////////////////////////////////////////////////////////////
session_start();

//ECHO "Session variable for species name is - {$_SESSION['species_name']}";  //Debug statement

error_reporting(-1); // Report every single error going.
ini_set('display_errors', 1); // Show them in-line.
set_time_limit(0); // No time limit.
// The rest of your code is on the next line and onwards.


?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML>
<head>
<title>TAXA: Ultraviolet Wood Fluorescence</title>


</head>

<body bgcolor='ivory'>

<table align='center' cellpadding='5', Cellspacing='0' border =0>
<tr>
	<td valign='top'>
		<form name='NewWin'>
			<p><A href="javascript: self.close ()">Close this Window</A></p>
		</form>
	</td>
</tr></table>


<?PHP

# ////////////////////////////////////////////////////////////////////////////////////
#				            CONNECT TO DATABASE
# ////////////////////////////////////////////////////////////////////////////////////



include ("connecttotaxa.php");
$connection = mysql_connect($hostname, $username, $password) 
	or die("Unable to connect to database server");

$dbname="taxa";
$db = mysql_select_db($dbname, $connection)
	or die("Unable to connect to database");
/* 
--------------------------------------------------------------------------------
				            HOPEFULLY CONNECTED AT THIS POINT
---------------------------------------------------------------------------------- 
*/

///////////////////////////////////////////////////////////////////////////////////////////////////////
// 								SECTION TO DISPLAY WOOD SCANS
/////////////////////////////////////////////////////////////////////////////////////////////////////

	Echo "<div align = 'center'>";

	Echo "<H2 align=\'center\'>Ultraviolet Fluorescence Photo of 
	{$_SESSION["speciesname"]}</H2>";
	
	Echo "<p align='center'><I>(Only 10 to 15% of all wood species fluoresce under UV)</I></p>";
	//Echo "Line 74<br />";
		
	$speciesname = $_SESSION["speciesname"]; //Successful transfer of ssssion parameter to a variable.
	//Echo "The species name is - $speciesname<br />"; //Proof that it transferred properly

	//$uvquery = "SELECT * FROM uvphotos WHERE 'species_name' = $speciesname";
	
	$speciesname = mysql_real_escape_string($speciesname);
	
	//ECHO "\$speciesname, line 73 is - $speciesname<br />";
	
	$uvquery = "
	SELECT u.uv_filename FROM uvphotos u JOIN species s ON u.species_name=s.species_name WHERE u.species_name = '$speciesname'";
	
	//WHERE u.species_name = 'Acacia aneura'";
	

	//ECHO "Line 85 -<br /> $uvquery<br />"; //debug statement

	$uvresult = mysql_query($uvquery) or die(mysql_error());
	
	if(!$uvresult) 	
		die(mysql_error())
		; 

	//ECHO "<br />\$uv_filename is - $uv_filename<br />";


	$numrows = mysql_num_rows($uvresult) or die (mysql_error());
	//ECHO "Number of rows is - $numrows<br />";

	if(mysql_num_rows($uvresult) >0){
		$row = mysql_fetch_array($uvresult);
		//ECHO "\$row on line 99 is - $row<BR />";
	};
	
	//$row = mysql_fetch_array($uvresult);
	//ECHO "\$row on line 104 is - $row<BR />";
		
		
		EXTRACT($row);	
	//ECHO "\$row on line 108 is - $row<BR />";
	//ECHO "The path and file name, line 120 is $row('uv_filename')<BR />";
	
	$uv_filename = $row['uv_filename'];

	//ECHO "the uv photo name is - $row[0]<br />";
	
	$uvpath = "./uv/$uv_filename";

	ECHO "<img src='$uvpath'><br />";
	

  Echo "<br />END OF PICTURE AREA<br /><br />";

	//ECHO "<HR>";

Echo "</div>";



?>

<!-- <h4 align="center">Webmaster: This function is still Under Construction</h4> -->



<table align='center' cellpadding='5', Cellspacing='5' border =1>
<tr>
	<td valign='top'>
		<form name='NewWin'>
			<p><A href="javascript: self.close ()">Close this Window</A></p>
		</form>
	</td>
</tr></table>

</body>
</HTML>

With over 15,000 wood names in the wood knowledge base I am building (at www.prowebcanada.com/taxa) it has become practically essential to add a versatile search engine to extract queries from the MySQL files. I started with an online template and have brought it quite a way along but some things still do not work.

The file name is multisearch.php. Additions I have made allow users to pick amongst
5 different searches (ie. 5 different columns). That part, amen, is working fine!

The problems is with paging data in any query report. The search engine displays
only one page faithfully. It is supposed to display the rest page by page or back
a page through another button. This happens through incrementing or decrementing
the record counter and aiming back to the same code with PHP_SELF statements.

When I try to go on to the second page of a report, I do not get any PHP serious
errors but instead:

Search Results

Sorry, your search: "Oak" returned zero results
Couldn't execute query

(At one point, the forward direction was working. I don't know what changed.)
Trying to go back a page gives identical comments.

I have considerable internal documentation added to make it easier to understand
and copious parameter echos (some commented out, some not). Once this search
service is working properly, it will be a huge help in navigating all the data.
I sure look forward to getting this working! :-) .

I will include the full page of code.

===============================================================================

<?php
//session_start(); // start up your PHP session! 
	
	
	//$record_counter= @$_GET[$record_counter];
	//$record_counter = $_SESSION['$record_counter'];
	ECHO "\$record_counter on line 7 is $record_counter<br />";
	$count = $record_counter+1;

	//$count = $_SESSION['$count'];
	
	ECHO "\$count at the top, line 12 is - $count<br />";

//$_SESSION['$count'] = $count;
?>


<HTML>
<HEAD>
<!---
############################################################################################
This page contains the major search functions for The TAXA Wood Knowledge Base.
The file is commonname_search.php.
It is called from the main menu.
Important parameters include:
	$searchtype - is the method users choose to do a search as the English words
    $searchfield - Each method translates into the actual field in the 'species' table
    			   used to complete the query
	$querystring - is the actual query string submitted by the user.
    $record_counter is the record counter for the last record of the present page. First appears in line 146
	$newquerystring - The new query string value fom the form by using $_GET[]
	$limit - The allowed number of records reported before starting a new page
    connecttotaxa.php - holds the values needed to connect to the taxa database
	$searchfield - The variable to indicate what field to search on. Right now there is only one,
    			but coding has already been worked out for letting users choose from a list of
                columns. To be added in the next version
	$$numrows - The total number of rows in the present query result
    $count - is the lower record number in the next upcoming page (if one is needed)
    		It will always be one more than $record_counter which holds the highest record number for the
            cuurent page.
	$currPage - Calculates the current page number.            
	$Page - Like $currPage. I am not sure yet what the relationship between them is.            
	$remainder - The query shows in blocks of $limit records. If the total number of records 
    		to report is divided by the block size ($limit) and there is a remainder, then 
            a new page needs to be started to finish off the last records in the report.
	$news - Is the highest record number to use for the next page.
(..... or this is what I interpreted so far).                
    
This search engine was adapted from the template search engine at 
	http://www.designplace.org/scripts.php?page=1&c_id=25
############################################################################################
--->



<TITLE>TAXA: Multicolumn Search</TITLE>
<META name="Template author" content="Steve R, http://www.designplace.org/">
<META name="Bill Mudry" Content="TAXA Wood Knowledge Base web designer and author">
<STYLE type="text/css">
	body {
		background-color:'ivory'; color: 'navy'; font-family: verdana, arial, sans-serif; font-size: 12px;
		}
	.smalltext {font-size: 10px;}
	.mediumtext {font-size: 13px;}
	.largertext {font-size: 1.5em};
	.reportheader {font-size: 20px; font-weight:700;}
	
	td {font-size:0.8em; background-color: '#EFDFCF'}
	
	H1 {font-size:2em};  /* displayed at 24px */
	H2 {font-size:1.5em};  /* displayed at 18px */
	H3 {font-size:1.25em};  /* displayed at 15px */
	H4 {font-size:1em};  /* displayed at 12px */
	
	#formbox {float: left; border:3px dotted red;background-color:"skyblue";}

</STYLE>

</HEAD>
<!-- © http://www.designplace.org/ (For the template used to start this design) -->

<!--
=========================================================================================
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX END OF HEADER  XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX START OF BODY  XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
=========================================================================================
-->

<BODY>

  <?php 
  
	//parse_str($_SERVER['QUERY_STRING'], $qs);
	//$record_counter =$qs['record_counter'];   
  
	//$record_counter = 14; hard coded as a debug statement

  //ECHO "\$count on line 99 is - $count<BR />"; //debug statement

  ECHO "\$record_counter on line 95 is - $record_counter<BR />"; //debug statement
  ?>

<H2 align="center">Multi Column Search</H2>

<DIV id="formbox">

<?php
//querystring ="";
//$querystring ="";

  $newsearch = 0 ; //Equals 1 only on a new search request
  //ECHO "<p align=left>\$newsearch on line 111 is $newsearch</p>";
  $submit="";
  //ECHO "\Ssubmit, before the form, on line 113 is - $submit<br />";
  If ($submit) {$record_counter = 0;}
?>

<!--
=========================================================================================
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX MAIN FORM STARTS HERE  XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
=========================================================================================
-->

<FORM name="form" action="multisearch.php" method="get">

<!-- ADD A TRANPARENT INSIDE TABLE TO ORGANIZE PLACEMENT OF ALL THE FORM ELEMENTS -->
	<TABLE align='center' border='0' bgcolor='ivory' cellpadding='6'>
	    <TR>
    	    <TD align="center" colspan="5">
            <SPAN class="reportheader">Choose which search type you want first</SPAN>
            </TD>
    	</TR>
    	<TR>
         	<TD class="mediumtext">
            	<B>Common names</B>  
            	<Input type='radio' value='common names' name="searchtype" />
        	</TD>
        	<TD class="mediumtext">	
            	<B>Botanical names</B>  
            	<Input type='radio' value='botanical names' name="searchtype" />
        	</TD>
        	<TD class="mediumtext">
            	<B>Genus</B>  
            	<Input type='radio' value='genus' name="searchtype" />
        	</TD>
        	<TD class="mediumtext">
            	<B>Location</B>  
            	<Input type='radio' value='location' name="searchtype" />
        	</TD>
        	<TD class="mediumtext">	
            	<B>Description</B>  
            	<Input type='radio' value='description' name="searchtype" />
        	</TD>
    	</TR>
    	<TR>
        	<TD align="center" colspan="5" class="mediumtext">
      			<B>Enter a Search String:</B>&nbsp;
  
      		<INPUT type="text" size="40" name="querystring" />&nbsp;&nbsp;&nbsp;
			<INPUT type="hidden" name="newsearch" value="1">
			<INPUT type="submit" name="submit" value="Start Search" />
  			</TD>
 		</TR>
	</TABLE>

</FORM>
</DIV>

<!--
=========================================================================================
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX MAIN FORM STOPS HERE  XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
=========================================================================================
-->

<?php

$submit = $_GET["submit"];
ECHO "\$submit on line 179 is - $submit<br />";

$newsearch = $_GET["newsearch"];
//ECHO "\$newsearch just past the form on line 180 is - $newsearch<br />";

IF ($newsearch=1) {$record_counter=0;};
ECHO "\$record_counter on line 185 is - $record_counter<br />";
/*
===========================================================================
XXXXXXXXXXX GET THE SEARCH TYPE AND SEARCH STRING FROM THE FORM XXXXXXXXXXX 
===========================================================================
*/
  	$searchtype = $_GET['searchtype'] ;
	$searchtype = trim($searchtype); //trim whitespace from the stored variable  

	//ECHO "On line 192, \$searchtype is - $searchtype<br />"; // Debug statement
 
	$querystring = @$_GET['querystring'] ;
	$querystring = trim($querystring); //trim whitespace from the stored variable  	
	
  // =====================================================================================
	// REASSURE USER OF THEIR CHOICES. (ALSO GOOD FOR DEBUGGING).
	If (!empty($searchtype))
		{
			ECHO "<h3 align='center'>searching under $searchtype for: &quot;" . $querystring . "&quot;</h3>";
		}

		//ECHO "Line 204 - \$searchtype is - $searchtype<br />"; //debug statement
/*
===============================================================================
XXXXXXXXXXXXXXXXX  TRANSLATION OF SEARCH TYPE TO COLUMN NAME  XXXXXXXXXXXXXXXXX 
===============================================================================
*/

$searchfield = "";
	//ECHO "Line 196 - \$searchtype is - $searchtype<br />"; // Debug statement

	switch ($searchtype) {
		case "common names":
			$searchfield = trim("species_commonname");
			//ECHO "<br>common names - $searchfield<br>\n";
			break;
		case "botanical names":
			$searchfield = trim("species_name");
			//ECHO "<br>botanical names - $searchfield <br>\n";		
			break;
		case "genus":
			$searchfield = trim("genus_name");
			//ECHO "<br>genus - $searchfield <br>\n";		
			break;
		case "location":
			$searchfield = trim("location");
			//ECHO "<br>location - $searchfield <br>\n";		
			break;
		case "description":
			$searchfield = trim("species_description");
			//ECHO "<br>description - $searchfield <br>\n";				
			break;
		default: If (!isset($searchfield)) {
			ECHO "Please choose a search type first";
		}
	}
/*
===============================================================================
XXXXXXXXXXXXX END OF TRANSLATION OF SEARCH TYPE TO COLUMN NAME  XXXXXXXXXXXXXX
===============================================================================
*/


  // Get the search variable from URL. Old designations were $var = @$_GET['q'] ;

  $newquerystring = @$_GET['querystring'] ;
  $querystring = trim($newquerystring); //trim whitespace from the stored variable

	$count = $record_counter +1;
  $newsearch = @$_GET['newsearch'] ;
  ECHO "\$newsearch on line 255 is $newsearch";

  if ($newsearch > 0) {$record_counter=0;};

	/*=====================================================================
	XXXXXXXXXXXXX SET THE NUMBER OF RECORDS PER PAGE HERE  XXXXXXXXXXXXXX
	=====================================================================*/
	$limit='30'; 

// check for an empty string and display a message.
if ($querystring == "")
  {
  ECHO "<p align='center'>Please enter a search string...</p>";
  exit;
  }

// check for a search parameter
if (!isset($newquerystring))
  {
  ECHO "<p align='center'>We dont seem to have a search parameter!</p>";
  exit;
  }

/*=========================================================================================
  XXXXXXXXXXXXXXXXXXXXXXXXXXX Connect to the TAXA Database  XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
  =========================================================================================*/

include ("connecttotaxa.php");
$connection = mysql_connect($hostname, $username, $password) 
	or die("Unable to connect to database server");


//specify database ** EDIT REQUIRED HERE **
mysql_select_db("taxa") or die("Unable to select the TAXA database"); //select which database we're using


/*=========================================================
  XXXXXXXXXXXXXXX BUILD THE SQL QUERY  XXXXXXXXXXXXXXXXXXXX
=========================================================*/

// Build SQL Query  
$searchquery = "SELECT * 
FROM species 
WHERE $searchfield like \"%$querystring%\"  
  order by '$searchfield'"; 

 $numresults=mysql_query($searchquery);
 
 
 @$numrows=mysql_num_rows($numresults);

/*===============================================================================================
  XXXXXXXXXXXXXXX TEST FOR ZERO RESULTS AND PRINT A MESSAGE OF THAT IF TRUE  XXXXXXXXXXXXXXXXXXXX
================================================================================================*/
if ($numrows == 0)
  {
  ECHO "<h3 align='center'>Search Results</h3>";
  ECHO "<p align='center'>Sorry, your search: &quot;" . $querystring. "&quot; returned zero results</p>";
  }

/*=========================================================
// $record_counter is the record counter for the present record
// next determine if s has been passed to script, if not use 0
=========================================================*/

  if (empty($s)) {
  $record_counter=0;
  }

/*===========================================================================
  XXXXXXXXXXXXXXX QUERY THE DATABASE TO GET THE RESULTS  XXXXXXXXXXXXXXXXXXXX
===========================================================================*/

// get results
  $searchquery .= " limit $record_counter,$limit";
  $result = mysql_query($searchquery) or die("Couldn't execute query");

/*=========================================================================================
  XXXXXXXXXXXXXXX ECHO BACK TO THE USER WHAT THEY ASKED TO SEARCH FOR  XXXXXXXXXXXXXXXXXXXX
=========================================================================================*/
ECHO "<p align='center'>You searched for: &quot;" . $querystring . "&quot;</p>";

/////////////////////////////////////////////////////////////////
//          ********** REPORTING STARTS HERE  **********       //
/////////////////////////////////////////////////////////////////

/*================================================================
  XXXXXXXXXXXXXXX SET A TITLE FOR THE REPORT  XXXXXXXXXXXXXXXXXXXX
================================================================*/
ECHO "<H3 align='center'>Search Results - $numrows references found.<br /><br /></H3>";

// now you can display the results returned

/*==========================================================================
  XXXXXXXXXXXXXXX SET UP COLUMN HEADERS FOR THE REPORT  XXXXXXXXXXXXXXXXXXXX
==========================================================================*/

ECHO "<table align='center' border=0 cellpadding=3 Cellspacing=0 bgcolor='white'>";
ECHO "<tr colspan=3>";
	ECHO "<td bgcolor='#EFDFCF' class=\"reportheader\">";
		//ECHO "<span>Common Name</span>";
		ECHO "Common Name";
	ECHO "</td>";

	ECHO "<td bgcolor='#EFDFCF' class=\"reportheader\">";
		ECHO "Botanical Name";
	ECHO "</td>";

	ECHO "<td bgcolor='#EFDFCF' class=\"reportheader\">";
		ECHO "Authorities";
	ECHO "</td>";

	ECHO "<td bgcolor='#EFDFCF' class=\"reportheader\">";
		ECHO "Genus Name";
	ECHO "</td>";

	ECHO "<td bgcolor='#EFDFCF' class=\"reportheader\">";
		ECHO "Location";ECHO "</td>";

	ECHO "<td bgcolor='#EFDFCF' class=\"reportheader\">";
		ECHO "Comments";
	ECHO "</td>";

	if (!isset($count)){$count=1;};

ECHO "</tr>";
ECHO "<tr>";





/*========================================================
  XXXXXXXXX  PRINT OUT RECORDS TILL END OF QUERY XXXXXXXXX
  
  ************** WHILE LOOP STARTS HERE ******************
========================================================*/
  while ($row= mysql_fetch_array($result)) {
  $commonname = $row["species_commonname"];
  $botname = $row["species_name"];
  $authorities = $row["authorities_species"];
  
  $genusname = $row["genus_name"];
  $location = $row["location"];
  $comments = $row["comments"];

	ECHO "<td valign='top' class=smalltext;>";
	//ECHO "\$count is $count<br />";
	
	ECHO "$count &nbsp; $commonname<br />" ;
	ECHO "</td>";
	
	ECHO "<td valign='top' class=smalltext;>";
	ECHO "<a href=\"displayspecies.php?&species_name=$botname \">";
	ECHO "$botname<br />";
	ECHO "</a><br>\n";

	ECHO "</td>";
	ECHO "<td valign='top' class=smalltext;>";
	ECHO "$authorities<br />";
	ECHO "</td>";
	
	ECHO "<td valign='top' class=smalltext;>";
	ECHO "$genusname<br />";
	ECHO "</td>";

	ECHO "<td valign='top' class=smalltext;>";
	ECHO "$location<br />";
	ECHO "</td>";
	
	ECHO "<td class=smalltext;>";
	ECHO "$comments<br />";
	ECHO "</td>";

	ECHO "</tr>";
	ECHO "<tr>";

  $count++ ;
  $record_counter++ ; // DOESN'T $record_counter HAVE TO BE INCREMEENTED FOR EACH ROW PRINTED ALSO??
  // ANOTHER COPY WORKED A FEW MONTHS AGO WITHOUT IT. iT IS NOT IN THE TEMPLATE.
  //ECHO "\$record_counter on line 412 is - $record_counter<BR />"; //debug statement
  }
  

/*========================================================
  XXXXXXXXX  END OF WHILE LOOP. XXXXXXXXX
========================================================*/

////////////////////////////////////////////////////////////
//     CLOSE THE REPORT TABLE
////////////////////////////////////////////////////////////
ECHO "</tr>";
ECHO "</table>";
ECHO "<BR />";
ECHO "\$record_counter on line 436 at the end of the report loop is - $record_counter<br />";



// Calculate the current page
$currPage = (($record_counter/$limit) + 1);
$pages = $currPage; // 
ECHO "\$currPage on line 451 is - $currPage<br />"; //debug statement
ECHO "\$record_counter on line 452 is - $record_counter<BR />"; //debug statement

//break before paging
  ECHO "<br />";

  // next we need to do the links to other results

//////////////////////////////////////////////////////////////////////
// If $record_counter is less than 1, there are no more records to view backwards.
//////////////////////////////////////////////////////////////////////

// bypass PREV link if s is 0 since there must be at least one more record to display to use a new page.
if ($record_counter>=1) { 
  ECHO "\$record_counter at line 453 is - $record_counter<br /> "; //debug statement
  $prevs=($record_counter-$limit);
	  ECHO "\$prevs at line 455 is - $prevs<br /> "; //debug statement

//////////////////////////////////////////////////////////////////////////////////////////////
//    ****** This statement will run the prior page of the report. It is user chosen******
//  
//////////////////////////////////////////////////////////////////////////////////////////////
  print "&nbsp;<a href=\"$PHP_SELF?record_counter=$prevs&querystring=$querystring&searchfield=$searchfield\">
  <p align='left' class='largertext'><b>&lt;&lt; Previous $limit records</b></p></a>&nbsp&nbsp;"; // Page 357

}   //END OF PRESENT IF STATEMENT

// calculate number of pages needing links
  $pages=intval($numrows/$limit);

//ECHO "\$pages on line 469 is - $pages<br />"; //debug statement
//ECHO "\$numrows on line 470 is - $numrows<br />"; //debug statement
//ECHO "\$limit on line 471 is - $limit<br />"; //debug statement

// $pages now contains int of pages needed unless there is a remainder from division

  if ($numrows%$limit) {
  // has remainder so add one page
  $pages++;
  }
//ECHO "\$pages on line 479 is - $pages<br />"; //debug statement

$remainder = ($numrows%$limit);
//ECHO "\$remainder on line 353 is - $remainder<br />"; //debug statement

  ECHO "<br />";

	//$x = ($record_counter+$limit)/$limit
	//ECHO "\$x on line 478 is - $x<br />"; //debug statement
	ECHO "\$record_counter on line 479 is - $record_counter<br />"; //debug statement
	
	//ECHO "\$limit on line 490 is - $limit<br />"; //debug statement
	//ECHO "\$pages on line 491 is - $pages<br />"; //debug statement

// check to see if last page
  if (!((($record_counter+$limit)/$limit)==$pages) && $pages!=1) {

  ECHO "<br />";

  // not last page so give NEXT link
  $news=$record_counter+$limit;
	ECHO "\$record_counter on line 517 is - $record_counter<br />"; //debug statement
	//ECHO "\$limit on line 492 is - $limit<br />"; //debug statement
	//ECHO "\$news on line 493 is - $news<br />"; //debug statement
	//ECHO "<div align='right' style='color:'#FF0000'>"; 
	
////////////////////////////////////////////////////////////
//  This statement will run the next page of the report.
//  It is user chosen
////////////////////////////////////////////////////////////

//echo "&nbsp;<a href=\"$PHP_SELF?s=$news&q=$var\">Next 10 &gt;&gt;</a>";

ECHO "&nbsp;<a href=\"$PHP_SELF?record_counter=$news&querystring=$newquerystring&searchfield=$searchfield\">
<p class='largertext' aligm='right'><B>Next $limit records &gt;&gt;</b></p></a>";
  
 	ECHO "</div>";
  }
	ECHO "\$record_counter on line 534 is - $record_counter<BR />"; //debug statement

	$record_counter = $news;

	ECHO "\$record_counter on line 538 is - $record_counter<BR />"; //debug statement

//$_SESSION['$record_counter'] = $record_counter;
//$_SESSION['$count'] = $count;

?>

<!-- © http://www.designplace.org/ (original template) -->

</BODY>
</HTML>

Kieran, you and Sacha were right. Your code was especially useful since the
error disappeared using it. Thanks. This is the first time that I cane across
line numbering not always being referred to from the very top of the code page.

Further down the code I have some other problems but for now, I want to struggle
toward a solution a bit more by myself first before presenting it.

Bill Mudry
Mississauga, ON

Lately I have been learning how to use 'sessions' (with some degree of success) for transferring parameters between pages. I have come across a new error report that is so weird that I would not be able to envision how I could do a Google search to find what is happening. I am so close ----- and (so far) so far away from being able to display images in a pop-up window.

First, some background to get you going.
The website is at www.prowebcanada.com/taxa.

On the left side menu, clicking on 'Woody Species' will bring up a huge list of over 15,000 botanical wood names. Each is linked to bring up (dynamically) a data sheet on
the specific wood chosen.

Some woods show a picture (scan) of the wood, some do not have one. Just below that picture area (scan present or absent) are three buttons to bring up three pop-up windows:
Photomicrographs More Photos U.V. Fluoresence
Each of the new windows are planned to show the content of the titles. To get the appropriate image to show for the currently chosen wood requires supporting code to sort through stored photos and images and to display it. I chose to do the development first on the 'U.V. Fluorescence' pop-up. (Incidentally, I have found that as much as 10% - 15% of the 3,000 woods in my collection will actually glow under ultraviolet light and I found that I can photograph this).

Once I get any one of these windows working OK, it will become much easier to get all three pop-ups to display their images.

Each of these pop-up pages start with a session start statement so it can find what the currently chosen botanical name is. The page starts with a title of:
Woody Species (botanical name of the wood)

An example would be Woody Species Acacia aneura

When you pick a different wood, the page successfully changes the title to reflect the name of the newly chosen wood. That shows that the session is at least working properly.

The Problem
I get no errors on connecting to the database. Even the SELECT statement is read OK with no errors. When I bypass the path and file name coming from the database by hard coding it, the picture shows fine(!). However -- when I instead try to go the next step with a mysql_query statement, I get a really weird and fatal error (no window forms) reporting of:

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'aneura' at line 3".

However, all there is at line 3 is:
"session_start();"

No mention of 'aneura' is made at all. Furthermore, all species names are made from two words (not one). The current correct species name actually is
Acacia aneura, so the error has to be related to the hidden value in the session.
I have NO IDEA how to fix this or what the system actually is complaining about.

I will leave the page hard wired for now so anyone can see the objective of how I
want the page to show. I will also include the source code below for the page.

What is happening? Has anyone come across an error like this? I doubt I would be able to know what to search for on the Net for this one.

Even MORE important ----- How can I get this page to display the right photo for a pre-chosen botanical name? If you need more information I am glad to send it.
(recall that if I get this working, two other pop-ups will also become easier to
finish off).

(Once again, fingers and toes crossed!)

Bill Mudry
Mississauga, Ontario

----------------------------------------------------------------------------------

<?php
session_start();

//ECHO "Session variable for species name is - {$_SESSION['species_name']}";  //Debug statement
?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML>
<head>
<title>TAXA: Ultraviolet Wood Fluorescence</title>


</head>

<body bgcolor='ivory'>

<?PHP

# ////////////////////////////////////////////////////////////////////////////////////
#				            CONNECT TO DATABASE
# ////////////////////////////////////////////////////////////////////////////////////



include ("connecttotaxa.php");
$connection = mysql_connect($hostname, $username, $password) 
	or die("Unable to connect to database server");

$dbname="taxa";
$db = mysql_select_db($dbname, $connection)
	or die("Unable to connect to database");

	
/* 
--------------------------------------------------------------------------------
				            HOPEFULLY CONNECTED AT THIS POINT
---------------------------------------------------------------------------------- 
*/

///////////////////////////////////////////////////////////////////////////////////////////////////////
// 								SECTION TO DISPLAY WOOD SCANS
/////////////////////////////////////////////////////////////////////////////////////////////////////


	Echo "<div align = 'center'>";

	Echo "<H2 align=\'center\'>Ultraviolet Fluorescence Photo of 
	{$_SESSION["speciesname"]}</H2>";

	echo "<br />";

	$uvquery = "SELECT * 
	FROM uvphotos
	WHERE species_name = {$_SESSION["speciesname"]}";
	

	//$resultuv = mysql_query($uvquery) or die(mysql_error());
	//mysql_query($uvquery) or die(mysql_error());

	ECHO "\$uvfilename on line 62 is - $uvfilename<br /><br />";

	$uvfilename = "uva/acacia_anura-mulgaUV01-800.JPG";
	$uvpath = "./uv/$uvfilename";

	ECHO "<img src='$uvpath'><br />";
	
	ECHO "<HR>";

  Echo "<br />END OF PICTURE AREA<br /><br />";

Echo "</div>";



?>

<h4 align="center">Webmaster: This function is still Under Construction</h4>



<table align='center' cellpadding='10', Cellspacing='20' border =1>
<tr>
	<td valign='top'>
		<form name='NewWin'>
			<p><A href="javascript: self.close ()">Close this Window</A></p>
		</form>
	</td>

</tr
></table>

</body>
</HTML>

Sometimes a person can get too close to their work that they cannot see what is going on wrong. I hope that is the case here.

I have been working on my favorite project, a large wood knowledge base at:
www.prowebcanada.com/taxa.
There is enough data to look up already and much, much more to follow, enough that
the site really does need a versatile internal search engine to navigate to what
users will want. A few months ago, I was able to install one that searched on just one field of the species table that worked fine. The algorithm and code came from a free script on the Internet. It included some validity checks, paging results and other sensible extras.

It worked but I had ideas on how it could be expanded into a search function
that could allow users to choose which table column to search to provide answers
to a query. I started by making a new form for user input on this via radio buttons. That part worked but
I verified what I had read ---- that you cannot use two forms on the same page
successfully.

Just over recent days I started over again, incorporating both the choice of what
kind of search a user wanted (by what column, common names, botanical names, genus,location and description) and the search string into one form. The search "types" get translated into each equivalent column name. That part is now working fine.

It is not the first time by far that I have had to get data out of a MySQL table
and, even with difficulty sometimes, have eventually been successful. For some
reason, I am having difficulty getting out more than zero records on all query
requests. I checked the general code out via books and an online tutorial. From one of them I was able to add a few lines that reported that there is a proper
connection to the database with no errors showing. I have left these un-commented
to show this on the website.

The original search code has more checks and features than is now showing because
I thought I should have a much simpler output just to concentrate on getting access to show multiple records for a query to start with. I know that once data
finally starts showing, it should be relatively easy to add back in the rest of the code temporarily removed.

I am hoping this is some elementary reason why a query brings back only zero
records. At this point, I am hoping someone else can spot why the data is not
coming forth. It should but it is not.

Ignore the extra CSS statements and the copious extra debug statements that I keep commented out. The user chosen field name and query string is copying into
the query statement fine.

Much thanks for your efforts :-)

The code now follows:

===============================================================================

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

<HEAD>
<!---
############################################################################################
This page contains the major search functions for The TAXA Wood Knowledge Base.
It is called from the main menu.
Important parameters include:
	$searchtype - is the method users choose to do a search as the English words
    $searchfield - Each method translates into the actual field in the 'species' table    			   used to complete the query
	$querystring - is the actual query string submitted by the user.
############################################################################################
--->

	<TITLE>TAXA: Search the Species Database</TITLE>
	<META name="author" content="Steve R, http://www.designplace.org/">
	<style type="text/css">
	<!--
		body {
			font: 100% Verdana, Arial, Helvetica, sans-serif; font-size: 11px;
			background: ivory;
			margin: 0; /* it's good practice to zero the margin and padding of the body element to account for 
			differing browser defaults */
			padding: 0;
			text-align: center; /* this centers the container in IE 5* browsers. The text is then set to the left 
			aligned default in the #container selector */
			color: #000000;
		}
		
		*/
		.oneColElsCtrHdr #container {
			width: 146em;  /* this width will create a container that will fit in an 800px browser window if text is 
			left at browser default font sizes */
			background: #FFFFFF;
			margin: 0 auto; /* the auto margins (in conjunction with a width) center the page */
			border: 1px solid #000000;
			text-align: left; /* this overrides the text-align: center on the body element. */
		}
		.oneColElsCtrHdr #header { 
			background: #DDDDDD; 
			padding: 0 10px 0 20px;  /* this padding matches the left alignment of the elements in the divs that 
			appear beneath it. If an image is used in the #header instead of text, you may want to remove the 
			padding. */
		} 
		.oneColElsCtrHdr #header h1 {
			margin: 0; /* zeroing the margin of the last element in the #header div will avoid margin collapse 
			- an unexplainable space between divs. If the div has a border around it, this is not necessary as that 
			also avoids the margin collapse */
			padding: 10px 0; /* using padding instead of margin will allow you to keep the element away from the edges of the div */
		}
		.oneColElsCtrHdr #mainContent {
			padding: 0 20px; /* remember that padding is the space inside the div box and margin is the space outside the div box */
			background: #FFFFFF;
		}
		.oneColElsCtrHdr #footer { 
			padding: 0 10px; /* this padding matches the left alignment of the elements in the divs that appear 
			above it. */
			background:#DDDDDD;
		} 
		.oneColElsCtrHdr #footer p {
			margin: 0; /* zeroing the margins of the first element in the footer will avoid the possibility of margin 			collapse - a space between divs */
			padding: 10px 0; /* padding on this element will create space, just as the the margin would have, without 			the margin collapse issue */
		}
		p.webnote {margin: 20px; font: Verdana, Arial, Helvetica, sans-serif; font-size:small;
		text-align: left;}

		.choose_search {margin: 8px; font: Verdana, Arial, Helvetica, sans-serif; font-size: 20px; color: navy;}

		.tabledata {font: Verdana, Arial, Helvetica, sans-serif; font-size:small; text-align: left;}
		
		
		-->
		</style>

</HEAD>
<!--
=========================================================================================
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX END OF HEADER  XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX START OF BODY  XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
=========================================================================================
-->

<BODY bgcolor="#FEFEBB">
<!--
=========================================================================================
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX MAIN FORM STARTS HERE  XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
=========================================================================================
-->
<!-- ADD A MAIN TITLE FOR THE PAGE -->
<H2 align="center">Search the TAXA Species Database by Chosen Fields</H2>

<!-- ADD AN OUTSIDE TABLE FOR A BORDER FOR ALL THE FORM ELEMENTS -->
<table  border="3" cellpadding="7" bgcolor="antiquewhite" align="center">
<tr>
	<td>
        
	<!-- ADD A TRANPARENT INSIDE TABLE TO ORGANIZE PLACEMENT OF ALL THE FORM ELEMENTS -->
	<TABLE align="center" border=0 bgcolor='ivory' cellpadding=10>
	    <TR>
    	    <TD align="center" colspan="5">
            <span class=".choose_search"><b>Which search type do you want?</b></span>
            </TD>
    	</TR>
    	<TR>
 
    <form name="search" action="<?php echo $_SERVER['PHP_SELF']; ?>" method=\"_get\">
        	<TD class="tabledata">
            	<B>Common names</B>  
            	<Input type='radio' value='common names' name="searchtype" />
        	</TD>
        	<TD class="tabledata">	
            	<B>Botanical names</B>  
            	<Input type='radio' value='botanical names' name="searchtype" />
        	</TD>
        	<TD class="tabledata">
            	<B>Genus</B>  
            	<Input type='radio' value='genus' name="searchtype" />
        	</TD>
        	<TD class="tabledata">
            	<B>Location</B>  
            	<Input type='radio' value='location' name="searchtype" />
        	</TD>
        	<TD class="tabledata">	
            	<B>Description</B>  
            	<Input type='radio' value='description' name="searchtype" />
        	</TD>
    	</TR>
    	<TR>
        	<TD align="center" colspan="5">
      			<b>Enter a Search String:</b>&nbsp;
      			<INPUT type="text" siz="40" name="querystring" />&nbsp;&nbsp;&nbsp;
                <INPUT type="submit" name="Submit" value="Start Search" />
    	</FORM>

		<!-- TURN OFF THE FORM TABLE -->
			</td>
		</tr>
		</table>
        
	</td>
</tr>
</table>


<!--
=========================================================================================
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX MAIN FORM STOPS HERE  XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
=========================================================================================
-->

<?php

/*
===========================================================================
XXXXXXXXXXX GET THE SEARCH TYPE AND SEARCH STRING FROM THE FORM XXXXXXXXXXX 
===========================================================================
*/
  	$searchtype = @$_GET['searchtype'] ;
	$searchtype = trim($searchtype); //trim whitespace from the stored variable  

	//Echo "On line 165, \$searchtype is - $searchtype<br />"; // Debug statement
 
	$querystring = @$_GET['querystring'] ;
	$querystring = trim($querystring); //trim whitespace from the stored variable  	
	
  // =====================================================================================
	// REASSURE USER OF THEIR CHOICES. (ALSO GOOD FOR DEBUGGING).
	If (isset($searchtype))
		{
			echo "<h3 align='center'>searching under $searchtype for: &quot;" . $querystring . "&quot;</h3>";
		}

		//echo "Line 177 - \$searchtype is - $searchtype<br />"; //debug statement
/*
===============================================================================
XXXXXXXXXXXXXXXXX  TRANSLATION OF SEARCH TYPE TO COLUMN NAME  XXXXXXXXXXXXXXXXX 
===============================================================================
*/

$searchfield = "";
	//echo "Line 142 - \$searchtype is - $searchtype<br />"; // Debug statement

	switch ($searchtype) {
		case "common names":
			$searchfield = "species_commonname";
			//Echo "<br>common names - $searchfield<br>\n";
			break;
			
		case "botanical names":
			$searchfield = "species_name";
			//Echo "<br>botanical names - $searchfield <br>\n";		
			break;
		case "genus":
			$searchfield = "genus_name";
			//Echo "<br>genus - $searchfield <br>\n";		
			break;
		case "location":
			$searchfield = "location";
			//Echo "<br>location - $searchfield <br>\n";		
			break;
		case "description":
			$searchfield = "species_description";
			//Echo "<br>description - $searchfield <br>\n";				
			break;
		default: If (!isset($searchfield)) {
			echo "Please choose a search type first";
		}
}
/*
===============================================================================
XXXXXXXXXXXXX END OF TRANSLATION OF SEARCH TYPE TO COLUMN NAME  XXXXXXXXXXXXXX
===============================================================================
*/

	/*
	=====================================================================
	XXXXXXXXXXXXX SET THE NUMBER OF RECORDS PER PAGE HERE  XXXXXXXXXXXXXX
	=====================================================================
	*/
	$limit=30; 


	// check for an empty string and display a message.
	if ($querystring == "")
	  {
	  	echo "<p>Please enter a search string...</p>";
	  	exit;
	  }

	// Check for a search parameter. If not entered yet, prompt for a search string.
	if (!isset($querystring))
	  {
		echo "<p>We dont seem to have a search parameter!</p>";
	  	exit;
	  }

/*
=============================================================
XXXXXXXXXXXXXXX CONNECT TO THE DATABASE  XXXXXXXXXXXXXXXXXXXX
=============================================================
*/
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");


$sql="show status";
$statusresult = mysql_query($sql);
if ($statusresult == 0)
	echo "<b>Error " . mysql_errno() . ": "
	. mysql_error() . "</b><br />";
  else echo "No connect error found<br />";	
// Ie. - The connection to the database seems fine.

/*
=========================================================
XXXXXXXXXXXXXXX BUILD THE SQL QUERY  XXXXXXXXXXXXXXXXXXXX
=========================================================
*/
	
	$querytaxa = "SELECT * 
	FROM species 
	WHERE '$searchfield' 
	LIKE \"%$querystring%\" ";
	// ORDER BY '$searchfield'";
/*
//////////////////////////////////////////////////
//	xxxxxxx QUERY DEBUG STATEMENTS ONLY xxxxxxx //
//////////////////////////////////////////////////
*/
	/*
	$querytaxa = "SELECT * 
	FROM species 
	WHERE 'species_commonname' 
	LIKE '%Elm%'"; 
	
	//ORDER BY 'species_commonname' "; // add back in after debugging.
	

	echo "vardumpquery is - ";var_dump($querytaxa); 
	//var_dump($searchfield); echo "vardumpsearchfield<br />";
	//var_dump($querystring); echo "vardumpquerystring<br />";

/*
=========================================================
XXXXXXXXXXXXXXXXXX END OF QUERY BUILD  XXXXXXXXXXXXXXXXXXXXX
=========================================================
*/


$result=mysql_query($querytaxa);
$rowcount=mysql_numrows($result);
echo "Row count is - $rowcount";
echo "<br />";
// mysql_close();
?>
<br />
<table align="center" border="1" cellspacing="2" cellpadding="2">
<tr>
<th><font face="Arial, Helvetica, sans-serif">Common Names</font></th>
<th><font face="Arial, Helvetica, sans-serif">Botanical Names</font></th>
<th><font face="Arial, Helvetica, sans-serif">Genus</font></th>
<th><font face="Arial, Helvetica, sans-serif">Location</font></th>
<th><font face="Arial, Helvetica, sans-serif">Description</font></th>
</tr>

<?php
$i=0;
while ($i < $rowcount) {

$f1=mysql_result($result,$i,"species_commonname");
$f2=mysql_result($result,$i,"species_name");
$f3=mysql_result($result,$i,"genus_name");
$f4=mysql_result($result,$i,"location");
$f5=mysql_result($result,$i,"species_description");
?>

<tr>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f1; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f2; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f3; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f4; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f5; ?></font></td>
</tr>

<?php
$i++;
}
?>

<!-- 
This is the original search engine that I started with. It worked fine using only one
choice for searching common wood names. I have since expanded it considerably.
Search engine © [url]http://www.designplace.org/[/url] 
-->

</BODY> 
</HTML>

============================== End of Code ======================================

whiteyoh, how convenient that you happen to be online right now :-).
I already have columns for primary and foreign keys in the tables. As per my words just a while ago, when I did apply a full set of constraints, they became a ROYAL
PAIN instead of being a help. It stopped me from adding any new data using PHP or MySQL-front, frustrating enough that I took them all out. They stopped me cold from adding new data.

I would not be aware of how that would simplify the code I need to find what the parent in each relationship would be. How would the code I need look different compared to using JOIN statements?

I learned about database normalization as far back as 1985. I have not specifically heard before of third normal form" by boyce codd. However, if you look typically how the website is used, you can now travel down the one to many relationships but so far no convenience to travel upward. The end result has to be in terms of a combination of PHP and SQL statements. I know that because the answer is always in another table in a higher level of the tree, I will very likely need to use a Select ...... Join ...... statement. I just seem to be poor at getting the right statement.

To me, there are pros and cons to high level normalization. I could normalize the
overall schema I am using more so than it is but storage is so cheap now that sometimes a minor amount of duplication is a helpful convenience. I remember clearly when storage space was a dear commuodity. My species display page in particular is simpler than if all the data was fractionated into a longer list of tables.
I am not sure how you would feel further normalization would help when the relationship of a parent order from a child family, a parent family from a child
genus and a parent genus from a child species are all well defined. All other columns except perhaps index columns aren't even needed for the answers I am seeking.

At one point, I had the relationship defined by constraints. I found they only got very much in the way as I tried to set up more data in the tables, so I took them out. There is only one 'unique' constraint on the species table so that I could import new species from long lists. That worked beautifully. In one large import,
it avoided the new formation of over 1,600 duplicates(!).

If you would read more carefully instead of just skim reading, you will see that I took care to include all table and column names needed to come up with an answer.
It may seem a bit longer but that is so there should be everything needed to fully understand how to arrive at the actual code that will do the job.

All relationships in the TAXA wood tree are one to many. To have more than one parent for any of the children would be a violation of the whole effort of showing how all plants in the world are ultimately related to each other.

My hardest part for me is getting the right SELECT ....... JOIN ...... statement working that will dig out the parent for amy child (eg. the parent Order for a
chosen Family under that Order (or the parent Family of a genus or a parent genus
of a species). I just find joins hard to do without ending up with hard to handle errors.

On average, I Have been doing quite well on my "TAXA" Wood Wood Knowledge Base website in getting many of the features coded and working --- more than this slow
and amateur PHP coder that he could. I even got a search engine working yesterday,
something I was not sure that I could do. Arranging travel up the botanical tree
is one of the major tasks yet to be conquered in the whole plan for the site.

To help anyone to visualize what this is all like, I will include the URL for the site: http://www.prowebcanada/taxa

There are 4 entry points to the tree. If you have had any botanical training ever, you will recognize what these are (all to do with botanical taxons). There are 4 levels connected by three one-to-many relationships:
Order -----> Families
Family ----> Genera
Genus -----> Species
(i.e. - A hierarchical tree)

Once you enter into this botanical tree at any of these levels, traveling down the tree all the way to details on one wood (one species) works well (amen!).

Now I want to complete being able to go the other direction UP the tree. This means for any species, genus or family, finding the parent of that element. I suspect that this should be a fairly standard solution requiring a join between the present table and the parent table. I keep getting error including fatal errors whenever I try to craft the right SQL statement that will do this. My apologies for being so inept at this.

If I can get help just creating ONE of the three solutions, I am sure that I can get the remaining two to work. Lets choose going from a page that is displaying a family to be able to find its parent order. Here are some basic facts to guide you:
The database is called 'taxa"

The PHP file that displays each family
(our upward start point) is 'showfamilies.php'

Family data is stored in "sci_family"
Family names are stored in column "family_name".
We need to find what order is the parent for any one family being displayed.
The local key to equate from is column 'orderID'

The orders are displayed in file 'showorder.php'
but order data is stored in table 'sci_order'
This file also has a prime key (foreign key) of 'orderID"

Once I can have code to find the parent, there are two things I will want to do
with it. First, I simply will display the order parent on the family page. Secondly, I should have little trouble or none, crafting a link to travel UP the
tree to take the user to the display page for that parent order.

Later, after replicating the algorithm to the other two remaining relationships it should not be long before total travel up and down the tree will finally be complete. I have tried working on the required SQL statement but always come up
with errors. That is especially the help I need. Being able to travel both directions is one of the key functions planned for this knowledge tree.

I trust I have included the parameters and names you will need. I could provide the entire schema for both MySQL files ... but that really should not be needed and could even cloud and complicate thinking. I look forward to help from the group with thanks even in advance.

... Eagerly waiting for code suggestions,

Bill Mudry
Mississauga, Ontario Canada
(billmudry at rogers.com should you care to email me).

So far progress overall has been going well in general on my pet project I call the TAXA project (nickname for taxonomy). The heart of it is a botanical tree on all the woods of the world. Since viewing it can make it easier for any of you to understand and help, feel welcome to preview it at prowebcanada.com/taxa :-).

So far you can enter into this data tree at any of four levels (Order, Family, Genus or
Species). To this point of development, a person can travel down the knowledge tree but
not up. The tree represents multiple one to many relationships:
Order (top level) ---> Family ---> Genus ---> Species
All relationships are one to many.
Any of you that have had any biological training will likely be familiar with this.

Now I look forward to being able to let users travel upward in the tree.
- Each species will have one parent genus
- Each genus will have a unique parent (botanical) family
- Each family has a unique order it is under.

If I can get the SQL statement to go from one child to the parent, I know it won't be
hard to adjust it to fit the other levels. Family (child) to Order (parent) will do.
The database is called 'taxa'. I am using MySQL.
Practically all pages in the site are dynamically formed using PHP.
The engine is MYISAM. ut8_general_ci
The table for all woody orders is 'sci_order'
The index column is 'orderID'.
All orders are named in column 'order_name'
The family table is 'sci_family'
The index column is 'familyID'.
All families are named in column 'family_name'
The program that will show the parent order is showorder.php
The child family (where the link to the parent order is) is displayed by
families.php
This means that the result will be a link on the specific family page that will
make the right order data page show. Likewise, after I will design and install
such links on the other two lower levels (genus and species).

Eventually I have to encase the SQL solution in PHP statements so readers can just
click on a link to go upward bound. Having full PHP statements provided would be a
bonus but I know the heart of the solution is likely to be in the right SQL statements.
It was a close decision for me to post here or on the PHP forum :-).

As part of this .... Is a JOIN needed? I look forward to any proposed solutions.
For many of you I believe this should be easy. If anyone would wish to email me
directly, you can do so at billmudry at rogers.com.

Thank you in advance.

Bill Mudry
Mississauga, ON Canada

I think you are making one minor mistake intead of != you must use = sign in join condition.

INSERT INTO species_master (genera, name, species_name, authorities, species_source)

SELECT DISTINCT genera, name, species_name, authorities, species_source FROM tervuren_target
LEFT JOIN species_master
ON
tervuren_target.species_name = species_master.species_name
WHERE species_master.species_name IS NULL

I noticed that some of the field names had changed since the suggested code had been run.
Instead I ran the following code:

INSERT INTO species_master (genus_name, species_name, authorities_species, source)
SELECT DISTINCT genus_name2, species_name2, authorities_species2, source2 FROM tervuren_target
LEFT JOIN species_master
ON
tervuren_target.species_name2 = species_master.species_name
WHERE species_master.species_name IS NULL

----------------------------------------------------------------------------------

However I got the most unusual error come up:

Error
SQL query:

SHOW TABLE STATUS LIKE 'tervuren_target';

MySQL said:

#2006 - MySQL server has gone away

I have never seen an error like that! Whatever could be happening to make 'the
MySQL server go away'?? Weird!

I ran this twice and got the same error both times. Where do I go from here, now?

Bill Mudry
Mississauga, Ontario

Greetings, coders from a Canadian near Toronto.
I have been working on an online botanical tree of as many woods that I can identify as I can. To be sure, the following link is absolutely not any sales or promotional gimmick. I just want to make it easier for anyone helping in our forum to be able to see first hand what I am doing. Its a lot easier to relate then since this is not a typical personal, commercial or none profit site. Its purpose is unique in the world of websites as a knowledge base for wood around the world. This is becoming the new repository for over 8 years of research into wood that before was totally in one text file approaching 5.9 meg.
http://www.prowebcanada.com/taxa (taxa - short for taxonomy)

Except for a couple of pages (such as the intro page), the entire site is dynamically generated with PHP and data stored in MySQL tables. Even most of the HTML is not static but generated on the fly for each page request.

Although there is a lot of information that goes along with all the wood names, the emphasis on the research I am doing has been especially to identify and record as many woods in the world as I can ----> the more the better.

Until recently, I was reporting about 6,550 woods all by botanical names. Using common names instead just doesn't work, trust me. I also have a list of all the woods in the world's second largest wood collection in the Netherlands, the Tervuren wood collection. Even after
cleaning out any duplicates (from over 17,000) there are still over 11,300 wood names in my Tervuren MySQL table. When I can finally append all the NEW species names into my main (or master) 'species' table, my project will take a huge jump forward by better than doubling the number of woods in the world I can report.

To preserve the original files I am working on, I copied them over as 'tervuren_target' (where the new records would come from) and 'species' as species_master (the table into which the new Tervuren data should be appended without duplicates.

Once that is done, I can later rename the file or copy the 'species_master' over again to just 'species' that is crucial to the operation of the species level.

At one point, I was able to append all the files in tervuren_target'into 'species_master (over 17,000 records) but in excess of 3,000 duplicates came over even though I thought I had added code to prevent this.

I sought advice from someone else and it was suggested that the following script should do the job:

INSERT INTO species_master (genera, name, species_name, authorities, species_source)

SELECT DISTINCT genera, name, species_name, authorities, species_source FROM 'tervuren_target'
LEFT JOIN species_master
ON
tervuren_target.species_name! = species_master.species_name
WHERE species_master.species_name IS NULL

---- but that did NOT work. I am a junior PHP and SQL programmer, slow at coding. When things don't work, I often end up trying all kinds of variations and that is what I did --- still stuck on not finding the script that will work.

Since both tables can be cleaned of duplicates within each separate file (such as with a SELECT DISTINCT statement) and because to all I have ever seen, DISTINCT can be used ONLY internal to one file at a time and not across files (am I right?), using a DISTINCT command is useless as long as the two files are cleaned of duplicates before the merge.

So ...... please help me craft an SQL statement that will append all names that are in 'tervuren_target' that are NOT in the 'species_master' file --- into the 'species_master'. The comparison should be on species_name. The end result should have no duplicates of 'species-name'.

Fingers-crossed, legs crossed, eyes even crossed, I hope someone can come up with a statement that will actually work.

With thanks in advance,

Bill Mudry
Mississauga, Ontario Canada
(Canada's 6th largest city).