The website I have been working on (as time permits) reports over
15,000 woods. With that much data, an internal search engine has
become mandatory for users to find what they are looking for. Almost
all the pages are dynamically formed using PHP and data stored in
MySQL files.
Therefore, a search engine that works on hard coded pages would be
totally unusable. I needed one that can search through MySQL tables.
I found a decent template for such a search engine at:
http://www.designplace.org/scripts.php?page=1&c_id=25
The original template works on searching one column in one table and
reporting only from that one column. The part of the data that by far
most users will wish to search is the species table. I wanted the search
report to show data keyed by one column but displaying multiple
columns --- so I knew I had some additional coding to do..
I
A while back I actually got a copy working for a while, broke it trying to
change it over to working with multiple column choices and then didn't
have a copy left of the code that worked.<live and learn :-[ >.
I have come a fair way to getting it to work but it won't page properly.
It has a parameter ($limit, presently set to 10 records) to control how
many records can be printed per page). It prints one page but gets
stuck. It has "<<prev" and "next>>" links for users to go backward or
forward in pages.
I have put in hours heavily adding internal documentation, studying the
logic till I understood most of it and plastering ECHO statements on
parameters to understand what is happening.
I think I know now what is wrong. $s is the record counter. Once its
value in multiples has a run over for the current page, a new page is
created by incrementing the record counter as the low end record for
the next page for forward travel and decrementing
for going backwards. The next page (or prior
page) is printed using $PHP_SELF statements
to run the same query again but in the prior or next page.
It took a lot of study , but as far as I can tell (..... and prove me wrong if
I am), when the program is turned back on itself
(using $PHP_SELF statements, whether for
incrementing or decrementing pages), the
value of $s near the top of the program gets lost. It has no chance to
accumulate (or reduce for backward travel). Therefore the program
gets locked into showing only the first page.
To test this, I hard set $s to a higher value, and ran a query. It
responded as it should, now reporting higher record numbers. As far as
I can see, repairing the paging problem seems to hinge on allowing
$s to carry over to be usable at the top of the program when
$PHP_SELF turns the program on itself.
What I am not familiar with is what the best way is to do that. What do
you suggest? Including code example if possible would be helpful?
If you also happen to spot any other serious bugs, please also let me
know. I am largely confident once $s can carry forward on each
program reiteration, the search engine will work as it should, good
paging included.
Sharing the Near Future
I have already done quite a bit of work on code (that works) that allows
users to choose what column to search on (eg. common names,
botanical names, country of origin, etc.). Once this paging problem is
overcome, It should be fairly easy to expand the search engine to work
on multiple search categories (ie. columns). By that point, it will make
the knowledge base website immensely more powerful and useful
for users to get versatile access to all its stored data. I really look
forward to that!
I will include all the code for this page below. I am also keeping the
large number of debug statements in and active and showing on the
output in a browser when the program is run --- so you can follow what
values parameters have at each step. The search page can be
directly viewed at:
http://www.prowebcanada.com/taxa/commonname_search.php?querystring=Oak&Submit=Search
Much thanks. Your help makes it possible to go forward where
otherwise I would have a hard or impassible time.
Bill Mudry
Mississauga, Ontario
====================================================
[CODE]
<HTML>
<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.
$S 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
$s 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: Simple Common Name Search</TITLE>
<META name="author" content="Steve R, http://www.designplace.org/";>
<style type="text/css">
body {
background-color:'white'; color: 'navy';
font-family: verdana, arial, sans-serif; font-size: 80%;
};
.smalltext {font-size: 10px};
.largertext {font-size: 1.5em};
TD {font-size:0.8em}
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 */
</style>
</HEAD>
<!-- © http://www.designplace.org/ -->
<!--
=========================================================================================
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX END OF
HEADER XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX START OF
BODY XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
=========================================================================================
-->
<BODY bgcolor='ivory'>
<?php
//$s = 14; hard coded as a debug statement
$S= @$_GET['$s'] ;
Echo "\$s on line 69 is - $s<BR />"; //debug statement
?>
<H2 align="center">Simple Common Name Search</H2>
<div align="center" title="formbox" id="formbox">
<!--
=========================================================================================
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX MAIN FORM STARTS
HERE XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
=========================================================================================
-->
<FORM name="form" action="commonname_search.php" method="get">
<INPUT type="text" name="querystring" />
<INPUT type="submit" name="Submit" value="Search" />
</FORM>
</div>
<?php
// Get the search variable from URL
$newquerystring = @$_GET['querystring'] ;
$querystring = trim($newquerystring); //trim
whitespace from the stored variable
/*=====================================================================
XXXXXXXXXXXXX SET THE NUMBER OF RECORDS PER PAGE HERE XXXXXXXXXXXXXX
=====================================================================*/
$limit='10';
// 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
$searchfield = 'species_commonname';
//$searchfield = 'species_name';
//$searchfield = 'genus_name';
//$searchfield = 'species_description';
//$searchfield = 'location';
/*=========================================================
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>Search Results</h3>";
echo "<p>Sorry, your search: "" .
$querystring. "" returned zero results</p>";
}
/*=========================================================
// $S is the record counter for the present record
// next determine if s has been passed to script, if not use 0
=========================================================*/
if (empty($s)) {
$s=0;
}
/*===========================================================================
XXXXXXXXXXXXXXX QUERY THE DATABASE TO GET THE RESULTS XXXXXXXXXXXXXXXXXXXX
===========================================================================*/
// get results
$searchquery .= " limit $s,$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>";
/*=========================================================
XXXXXXXXXXXXXXX SET A TITLE FOR THE REPORT XXXXXXXXXXXXXXXXXXXX
=========================================================*/
echo "<H3 align='center'>Search Results<br /><br /></H3>";
// ADD 1 TO THE COUNTER FOR THE LATEST RECORD PRINTED FOR THE PRESENT PAGE
$count = 1 + $s ;
// now you can display the results returned
/*==========================================================================
XXXXXXXXXXXXXXX SET UP COLUMN HEADERS FOR THE REPORT XXXXXXXXXXXXXXXXXXXX
==========================================================================*/
Echo "<table align='center' border=1 cellpadding=10 bgcolor='white'>";
Echo "<tr colspan=3>";
Echo "<td bgcolor='#EFDFCF'>";
Echo "<b>Common Name</b>";
Echo "</td>";
Echo "<td bgcolor='#EFDFCF'>";
Echo "<b>Botanical Name</b>";
Echo "</td>";
Echo "<td bgcolor='#EFDFCF'>";
Echo "<b>Authorities</b>";
Echo "</td>";
Echo "<td bgcolor='#EFDFCF'>";
Echo "<b>Genus Name</b>";
Echo "</td>";
Echo "<td bgcolor='#EFDFCF'>";
Echo "<b>Location</b>";Echo "</td>";
Echo "<td bgcolor='#EFDFCF'>";
Echo "<b>Comments</b>";
Echo "</td>";
Echo "</tr>";
Echo "<tr>";
//Echo "<td>";
/*========================================================
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. $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>";
//ECHO "</td>";
$count++ ;
$s++ ; // DOESN'T $S 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 "\$s on line 265 is - $s<BR />"; //debug statement
}
/*========================================================
XXXXXXXXX END OF WHILE LOOP. XXXXXXXXX
========================================================*/
Echo "\$s on line 272 is - $s<BR />"; //debug statement
////////////////////////////////////////////////////////////
// END OF WHILE LOOP.
////////////////////////////////////////////////////////////
Echo "</tr>";
Echo "</table>";
////////////////////////////////////////////////////////////
// CLOSE THE REPORT TABLE
////////////////////////////////////////////////////////////
ECHO "<BR />";
Echo "\$s on line 288 is - $s<BR />"; //debug statement
$currPage = (($s/$limit) + 1);
//$pages = $currPage; //
ECHO "\$currPage on line 292 is - $currPage<br />"; //debug statement
Echo "\$s on line 286 is - $s<BR />"; //debug statement
//break before paging
echo "<br />";
// next we need to do the links to other results
/*=========================================================----------
// If $s is less than 1, there are no more records to view backwards.
===================================================================*/
if ($s>=1) { // bypass PREV link if s is 0
ECHO "\$s at line 297 is - $s<br /> "; //debug statement
$prevs=($s-$limit);
////////////////////////////////////////////////////////////
// This statement will run the prior page of the report.
// It is user chosen
////////////////////////////////////////////////////////////
//print " <a href=\"$PHP_SELF?s=$prevs&querystring=$var\"><<
print " <a href=\"$PHP_SELF?s=$prevs&querystring=$querystring\">
<p class='largertext'><b><< Prev $limit</b></p></a>  ";
}
// calculate number of pages needing links
$pages=intval($numrows/$limit);
ECHO "\$pages on line 313 is - $pages<br />"; //debug statement
ECHO "\$numrows on line 313 is - $numrows<br />"; //debug statement
ECHO "\$limit on line 313 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 325 is - $pages<br />"; //debug statement
$remainder = ($numrows%$limit);
ECHO "\$remainder on line 327 is - $remainder<br />"; //debug statement
ECHO "<br />";
//$x = ($s+$limit)/$limit
//ECHO "\$x on line 296 is - $x<br />"; //debug statement
ECHO "\$s on line 332 is - $s<br />"; //debug statement
ECHO "\$limit on line 332 is - $limit<br />"; //debug statement
ECHO "\$pages on line 332 is - $pages<br />"; //debug statement
// check to see if last page
if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {
ECHO "<br />";
// not last page so give NEXT link
$news=$s+$limit;
ECHO "\$s on line 343 is - $s<br />"; //debug statement
ECHO "\$limit on line 343 is - $limit<br />"; //debug statement
ECHO "\$news on line 343 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&querystring=$querystring\"><p
class='largertext' aligm='right'><B>Next $limit >></b></p></a>";
ECHO "</div>";
}
Echo "\$s on line 366 is - $s<BR />"; //debug statement
?>
<!-- © http://www.designplace.org/ -->
</BODY>
</HTML>
[/CODE]
--
PHP Windows Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
