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>
<INPUT type="text" size="40" name="querystring" />
<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: "" . $querystring . ""</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: "" . $querystring. "" 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: "" . $querystring . ""</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 $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 " <a href=\"$PHP_SELF?record_counter=$prevs&querystring=$querystring&searchfield=$searchfield\">
<p align='left' class='largertext'><b><< Previous $limit records</b></p></a>  "; // 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 " <a href=\"$PHP_SELF?s=$news&q=$var\">Next 10 >></a>";
ECHO " <a href=\"$PHP_SELF?record_counter=$news&querystring=$newquerystring&searchfield=$searchfield\">
<p class='largertext' aligm='right'><B>Next $limit records >></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>