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>
<INPUT type="text" siz="40" name="querystring" />
<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: "" . $querystring . ""</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 ======================================
