Hey, gang: I know there are some MySQL-ous wonks on this list and I'm
hoping you might have some ideas about a search result problem:

I'm using an extension called "Advance Search" with my Dreamweaver
CS3. This auto-builds a search field and the associated query code for
every field you want, and returns the results on a new page.

I have a multiple select box with values K through 12 (grades); the
user can select any combination of grades. (The multiple select was my
way of avoiding having to figure out how to enable users to submit
"k-5" or "7-9", given this is a "canned" extension and I don't want to
tweak a bunch.)

I have a total of 21 records for now.

If I select any range, contiguous or otherwise, from K through 10, I
get the correct number of returns (often 19-21).

When I add either grade 11 or 12 (or both) to any other combination of
selections that previously had return >18 records, I always a lesser
number (18).

My guess is that the data field was formatted incorrectly (varchar),
and the numbers are being read as follows (commas are part of the data):

1, 10, 11, 12, 2, 3, 4, 5, etc.

I know too little to determine whether this is actually the cause of
the problem and, if so, what the solution would be (not to mention,
I'm really lacking on nomenclature, which can be a real hindrance when
searching for answers). I also wonder if the commas factor into the
problem.

Further, because this code was automatically generated by the
extension, I'm not sure how to modify it and would hope that I could
redefine the attributes of the data in that field instead (these don't
have be treated as *real* numbers that would be used for math
calculations).

I hope to spare any would-be helper from looking at the overall code,
and instead provide the chunk that specifically responds to the grade
data request:

$fieldValue = $HTTP_GET_VARS['gradeLevel'];
if(isset($HTTP_GET_VARS['gradeLevel']) && $HTTP_GET_VARS['gradeLevel']!
= "")
{
if($hasSQLWhereClause == false)
{
$hasSQLWhereClause=true;
$whereClause=$whereClause." rtrim(ltrim(upper(gradeLevel))) like
rtrim(ltrim(upper('%$fieldValue%')))";
}
else
{
$whereClause=$whereClause." and rtrim(ltrim(upper(gradeLevel)))
like rtrim(ltrim(upper('%$fieldValue%')))";
}
}

If the problem is not the data field, I'm betting the $whereClause
definition is it. Any time/effort you can put into this is greatly
appreciated. Thanks.

Marcus Duke
Webmaster/Info Specialist
UW Aquatic & Fishery Sciences


--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/GoLive/Browse/
Manage your subscription: http://www.ListSearch.com/GoLive/